Naming Cell Worksheet Independent

[LiamD]

When I name a cell it specifies the worksheet by default.

What I want is to copy a formula to a number of worksheets.

So my formula would be =ItemPrice*NumItems

Where ItemPrice and NumItems are names of the cell on the current worksheet.
i.e. ItemPrice is =Sheet1!$B$2
and NumItems is =Sheet1!$B$3

Now this formula would be repeated on the same cells on a number of worksheets.

I want to be able to defien the values liek this:-

i.e. ItemPrice is =$B$2
and NumItems is =$B$3

So the values will be B2 and B3 for every sheet that I copy the formula to.


Can this be done?

Thanks
LiamD

I supose I could get around this my not using names so:
=ItemPrice*NumItems
becomes
= B2 * B3

But I think using names makes it more readable.

 

[cpod]

When you name the range delete the reference to the sheet:

=!A1 instead of =Sheet1!A1

So using ItemPrice range name in a formula will refer to cell A1 on the current sheet.

 

[Gates Is Antichrist]

I'm not sure what cpod is suggesting. That's new to me, and seems to malfunction.

The quickest way to achieve this seems to be copying entire worksheets, when you have that luxury. Named ranges on the source sheet become sheet-specific ones on the target(s). (The 'luxury' presumption is optimistic, I know). After doing this, if you copy formulas using the common range names, you get to deal with the confusing "conflict" messagebox created by some of the more challenged 'tards at M$

Also possibly quick for you would be to set the ranges with VBA.

The hard way is to set them manually, sheet by sheet. You are correct that there is benefit in using the descriptive names rather than B2 and B3, so perhaps it's worth the trouble. In most cases for me it seems like it is.

 

[Datsmart]

See Eric's idea's here:
http://www.mrexcel.com/board2/viewtopic.php?t=239797