Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
mr unreliable
 
Posts: n/a
Default 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   Report Post  
Jim Rech
 
Posts: n/a
Default

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   Report Post  
mr unreliable
 
Posts: n/a
Default


"Jim Rech" wrote in message
...
Names can be "local" or "global" in Excel.



  #4   Report Post  
Gord Dibben
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying multiple sheets from one book 2 another and undertake spec Pank Mehta Excel Discussion (Misc queries) 14 March 16th 05 05:41 PM
Can I rotate excel sheets so columns are rows & vice-versa (i.e.. JamesR Excel Discussion (Misc queries) 3 January 12th 05 03:18 PM
Columns in Excel will not allow user to click in them Kim Excel Discussion (Misc queries) 1 December 28th 04 07:37 PM
How do I compare two columns on seperate sheets and replace text . hag400 Excel Worksheet Functions 1 December 28th 04 03:32 PM
Multiple sheets selected twa14 Excel Discussion (Misc queries) 2 December 21st 04 12:15 PM


All times are GMT +1. The time now is 10:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"