using the same name on multiple sheets

[Guest]

I'm VERY new to Excel, so please bear with me. I'm creating a workbook with
multiple sheets in Excel 2003. All of the sheets have the same column names,
such as Qty, Unit Price, Material Cost, etc. I used the names in formulas,
which are also the same for all sheets, for example, Material Cost is
=Qty*Unit_Price. The formulas on the first sheet work wonderfully, but they
seem to be using the info from the first sheet for all the other sheets.
Also, If I'm on any sheet but the first & click a column name in the name
box, it takes me to that column, but on the first sheet. Did I do something
wrong when I created the names?

Any help would be greatly appreciated )

 

[firefytr]

No you didn't do anything wrong. When you create a defined name in
Excel, it is working cross-sheets. This means you can only have one
individual name which exists per work-book.- Sorry.

 

[Guest]

Rats! I thought this would be an easy project, but I guess I better learn
more about formulas, if I'll have to do them using cell references. Thank you
for your help, Firefytr )

 

[Jack Schitt]

When you define the names, define them as local to each sheet, thus:

Currently you have Qty defined as something like

Qty refers to Sheet1!$A$1:$A$100

If instead you define it as
Sheet1!Qty refers to Sheet1!$A$1:$A$100
then the name is dedicated to Sheet1 (note that you prefix the name with the
sheet name to localise it to that sheet)
Then you can separately define
Sheet2!Qty refers to Sheet2!$A$1:$A$100
and so on

In Sheet1, if you set a formula
=Qty*Unit_Price
then as you have not specified the sheet name it will refer to the local
name. In other words it will compile as
=Sheet1!Qty*Unit_Price

Furthermore, if you now duplicate Sheet1 (Edit/Copy or move sheet/copy
sheet)
then any local names will be duplicated into the new sheet, ie
Sheet3!Qty will already be defined and refer to Sheet3!$A$1:$A$100

Caution: Good practice to avoid having both local and global names in the
same workbook with the same name. You may want first to delete all your
global names before redefining them as local names.

It is a powerful tool in the hands of a beginner, but you may want to look
at NameManager add-in available from:
http://www.bmsltd.ie/MVP/Default.htm
that makes managing these issues less of a pain.