Home |
Search |
Today's Posts |
#1
|
|||
|
|||
looking for way to name columns (across sheets in wkbk)
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. |
#2
|
|||
|
|||
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 "mr unreliable" wrote in message ... | 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. | | |
#3
|
|||
|
|||
"Jim Rech" wrote in message ... Names can be "local" or "global" in Excel. |
#4
|
|||
|
|||
Select the column and InsertNameDefine
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 On Thu, 10 Feb 2005 15:28:29 -0500, "mr unreliable" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
Can I rotate excel sheets so columns are rows & vice-versa (i.e.. | Excel Discussion (Misc queries) | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) | |||
How do I compare two columns on seperate sheets and replace text . | Excel Worksheet Functions | |||
Multiple sheets selected | Excel Discussion (Misc queries) |