sheet name in Named Range

[binaryfingers]

Hi

I have 5 sheets formatted the same way.

I would like to use a named range to pull data from the sheets and would like to know if I can do one named range but have the sheet name used as the 'differentiator'

I also have the name of the sheet in Cell A1 if that helps, by using the FAQ 68-2561 'To display the Sheet Name'

Currently I have the following named ranges

sku1Forecast
=OFFSET('book 1.xls'!sku1XValues,4,0)

sku1Range
=OFFSET(PS2!$BC$6,0,0,1,COUNTA(PS2!$6:$6))

sku1Values
=OFFSET('book 1.xls'!sku1XValues,3,0)

sku1XValues
=OFFSET(PS2!$BC$6,0,MATCH(Summary!$C$1,'book 1.xls'!sku1Range,0)-1,1,13)

The PS2 is the part that I would like to be able to be automatically changed to the value of cell A1 so that it looks up locally on that sheet.

EG
I have sheets

PS2
GCC
UNI
PGF
XB1

Hope you can help me.

Thanks,

 

[xlbo]

use the INDIRECT function

see help files for details

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

 

[PeterMoran]

Hi,

By default Excel assumes all Range names are Global - available to all sheets in a workbook, and not able to be duplicated in the workbook. If you have ranges currently created you will need to delete and then re-create them as below.

You can use the same Range Name in different sheets by creating Local Ranges as follows:

Select the Range and enter the Sheet Name followed by the Range Name in the namebox separated by an exclamation mark such as Sheet1!Area1. If the sheetname has spaces or other special chars then the sheetname always has to be enclosed in apostrophes ('Sheet 1'!Area1). If you use Insert, Name, Define then you also insert the Sheetname with the Rangename exactly as above.

Now you will have several Range names called Area1, and to access them in their own sheet just use the Range name without the Sheet name (eg =Area1). To access them outside their own sheet you must include the sheetname in the reference (eg =Sheet1!Area1).

Good Luck!

Peter Moran