Excel - Local Charts 1

[binaryfingers]

Hi

I have visited this challenge before and despite numerous attempts I am still drawing a blank and really need your help.

I have 4 named ranges as follows

Sku2Forecast
=OFFSET('Sales Report Jan06-Dec06 new.xls'!Sku2XValues,4,0)

Sku2Range
=OFFSET(PS2!$D$6,0,0,1,COUNTA(PS2!$D$6:$HF$6))

Sku2Value
=OFFSET('Sales Report Jan06-Dec06 new.xls'!Sku2XValues,3,0)

Sku2XValues
=OFFSET(PS2!$D$6,0,MATCH(Summary!$C$1,'Sales Report Jan06-Dec06 new.xls'!Sku2Range,0)-1,1,16)

These named ranges work fine, but as you can see they reference a dedicated worksheet in the form of PS2.

I have tried using an indirect command to reference a cell that determines the sheet name, but I find that this just automatically replaces with the sheet name I am currently on when I create/modify the name range. For Example, if I modify the range on sheet PS3, then PS2 gets replaced with PS3. etc etc

I look forward to your expertise in helping work out how I can use the same named ranges on each sheet and get the local graph to work.

Thanks,

 

[SkipVought]

BF,

You've got stuff in your head that is not on the table here.

Please describe the workbook function and structure and the problem you are trying to solve, not the METHOD you are trying to use.

Skip,

_[red][/red]

 

[binaryfingers]

Skip

Thanks for the response.

I would like to dynamically plot the last 16 weeks from the current week sales in a chart.

I have several sheets, (7 currently)

In the past I have been able to use the above named ranges to dynamically show the sales and the dates etc and have it auto update each time the date week is changed. My challenge is that I have had to 24 skus per each 7 sheet and with the addition of new skus, it means maintaining a lot of named ranges.

Each worksheet is laid out in the same format, so I was hoping there was a way that I would only have to use 24 named ranges and each sheet would be able to use the named ranges but pull the data from the repsective sheet.

I have drawn a blank as the offset references the A work sheet, in this instace PS2, and I have not been able to find a way, even using indirect, to have it reference the local sheet and pull the appropriate data.

Thanks again Skip, hope I have covered all the info this time and look forward to your help as usual..

 

[SkipVought]

What are these 7 sheets?

Skip,

_[red][/red]

 

[binaryfingers]

The 7 sheets record the sales and provide an analysis of each sku in each department.

The graph is used for presentations to highlight the performance against forecast and against same time last year.

 

[SkipVought]

You REALLY make thisng difficult by chopping up you data into different sheets.

These ALL need to be in ONE TABLE. Each Sheet Name should be a data element (SKU) in your table.

Skip,

_[red][/red]

 

[xlbo]

I tend to agree with Skip but I'm interested in this one - have you tried something like this ?

=OFFSET(INDIRECT(Lookups!$A$1 & "!$D$6"),0,0,1,COUNTA(PS2!$D$6:$HF$6))

where you have a sheet called Lookups which holds the sheet to use....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[binaryfingers]

Each sheet is 1492 lines long as it breaks out analysis into a presentable format. I do not know tables well enough, I simply display the data in the cells and format accordingly.

I also am unsure how I could seperate the data out accordingly by having a sheet name as an element and then have a further 24 skus associated to that element.

Sorry, but it sounds too confusing for me. I am no way an expert with Excel, however I am of course keen to learn new techniques and functions/formulas....but it sounds like I need to walk before I run..

Thanks again for your responses...

 

[binaryfingers]

Hi Geoff

Yes I did, I had a control sheet that it referenced but again the cell could only provide one sheet in the appropriate cell.

I was hoping there would be some kind of function like local.sheet, as what I really need to do is reference $A$1 on each of the local sheets.

Thanks again for the response,

 

[xlbo]

no need for sheet references - that is what is causing the issue - try this:

=OFFSET(Indirect("$D$6"),0,0,1,COUNTA(Indirect("$D$6:$HF$6")))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

This is your hour!

It's really quite simple. This way you have only ONE sheet to maintain and format rather than 7.

There are several options. The simplest would be to select the SKU using the AutoFilter.

Skip,

_[red][/red]

 

[binaryfingers]

Geoff

Thanks for the advice, were getting there!

I can now get the local data produced dependent on which sheet is active, which is fantastic!

Below are the new named ranges.

Sku1Forecast =OFFSET(Sku1XValues,4,0)
Sku1Range =OFFSET(INDIRECT("$D$6"),0,0,1,COUNTA(INDIRECT("$D$6:$HF$6")))

Sku1Value =OFFSET(Sku1XValues,3,0)
Sku1XValues =OFFSET(INDIRECT("$D$6"),0,MATCH(Summary!$C$1,Sku1Range,0)-1,1,16)

However, now I am having difficulty calling them in the charts.

I have tried several different ways.

=Sku1Value
'The Formula you typed contains an error. Try one of the following.' Then a list of suggestions

=PS2!Sku1Value
'A Formula in this worksheet contains one or more invalid references'

='Sales Report Jan06-Dec06 new.xls'!Sku1Value
'A Formula in this worksheet contains one or more invalid references'

As noted, I know that by doing a test on the range name using name manager analysis it produces the correct results.

any thoughts why the chart wont accept it?
Thanks again!

 

[xlbo]

Looks like you might be out of luck here - excel seems to need a sheet reference within the range name...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question