looking for way to name columns (across sheets in wkbk)

[mr unreliable]

hi Group,

I have named the columns of my first wksht as FY_2003, FY_2004, etc.

That works fine.

But, when I go to use those column names in a second worksheet,
it refers back to the first worksheet.

I am using the names as a way to specify the "intersection" of a
row column (maybe not the right terminology), such as
"Inventory FY2000".

That formulation is working on sheet one, but gives an error on
sheet two.

I tried a "3D" definition, i.e., 'sheet1:sheet2'|$D;$D, which was
accepted by xl, but then the "intersection" failed. I suppose it was
expecting a column there instead of something with a "3D" aspect.

I would be grateful for any help. jw

p.s., I suppose I could just re-define the columns as separate names
for each sheet, but that strikes me as "ugly", and ought to be
unnecessary.

 

[Jim Rech]

Names can be "local" or "global" in Excel. The names you created were
global and can be seen on any sheet. Local names are seen only on the
sheets they are created on, so you can have the same name on different
sheets.

To create a local name "Yr2004" on a sheet named "Sheet1" you would activate
that sheet and then, in the Define Name dialog, you would enter the name as
"Sheet1!Yr2004" rather than just "Yr2004". Preceding the name with the
active sheet's name makes it a local name.

--
Jim Rech
Excel MVP
| hi Group,
|
| I have named the columns of my first wksht as FY_2003, FY_2004, etc.
|
| That works fine.
|
| But, when I go to use those column names in a second worksheet,
| it refers back to the first worksheet.
|
| I am using the names as a way to specify the "intersection" of a
| row column (maybe not the right terminology), such as
| "Inventory FY2000".
|
| That formulation is working on sheet one, but gives an error on
| sheet two.
|
| I tried a "3D" definition, i.e., 'sheet1:sheet2'|$D;$D, which was
| accepted by xl, but then the "intersection" failed. I suppose it was
| expecting a column there instead of something with a "3D" aspect.
|
| I would be grateful for any help. jw
|
| p.s., I suppose I could just re-define the columns as separate names
| for each sheet, but that strikes me as "ugly", and ought to be
| unnecessary.
|
|

 

[mr unreliable]

Names can be "local" or "global" in Excel.

 

[Gord Dibben]

Select the column and Insert>Name>Define

In the "refers to" box delete the sheetname portion so's it looks like this

=!A:A

Give it a name of FY_2003 and OK.

This name will refer to column A on each sheet in the workbook.

Same for selecting a row and naming.

=!1:1 and name it Inventory

Then on any sheet =FY_2003 Inventory will return what is entered in A1 of
that sheet.


Gord Dibben Excel MVP