Naming Cell Worksheet Independent

LiamD

New Member
Joined
Sep 3, 2006
Messages
33
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

cpod

Board Regular
Joined
Apr 2, 2002
Messages
124
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

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
 

Forum statistics

Threads
1,136,370
Messages
5,675,367
Members
419,566
Latest member
moni2277

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top