Hello,

I am trying to determine the best way to name cells automatically. I have a
worksheet named Shipper Pricing. It contains the price 7 different suppliers
charge us for delivery to over 70 destinations. As these may change at any
time, new suppliers added, new destinations added or old destinations
deleted, I cannot use absolute references, so must have each cell named.

I tried to use InserNameLabel to create cell reference names for each and
that appeared to work, as long as I was on the same worksheet, however, I
need those referenced names to appear on externally linked worksheets and
can't figure out to do that.

Pleae help. thanks.

For the first part of the question, there's an easier way than going through
Insert Name Label. Choose the cell (or cells) to be named and click in the
Name Box (where you see the cell address you've chosen as "A1" or "B99") and
type in the name there. You can also use the pull-down arrow associated with
it to see all the named ranges, click one and you'll be taken to that
location instantly.

As for referencing them in another worksheet in the same workbook, simple:
Assume a cell with name JoeShipperCo on Sheet1, you can reference that cell
simply as =JoeShipperCo anywhere else in the workbook, even as part of a
formula (assume it's a number for a moment) as = 44 + JoeShipperCo

If you want to be specific you can add sheet name to it as
=Sheet1!JoeShipperCo
or
= 44 + Sheet1!JoeShipperCo

If you need to reference it in another workbook, best to start out with the
both workbooks open so Excel can automatically take care of setting path when
you close the linked from workbook. In the second workbook the reference
would be like
=Book1.xls!JoeShipperCo

"Mike C" wrote:

Hello,

I am trying to determine the best way to name cells automatically. I have a
worksheet named Shipper Pricing. It contains the price 7 different suppliers
charge us for delivery to over 70 destinations. As these may change at any
time, new suppliers added, new destinations added or old destinations
deleted, I cannot use absolute references, so must have each cell named.

I tried to use InserNameLabel to create cell reference names for each and
that appeared to work, as long as I was on the same worksheet, however, I
need those referenced names to appear on externally linked worksheets and
can't figure out to do that.

Pleae help. thanks.

I guess I wasn't clear enough. I don't want to have to name all 490+ cells
individually.

I have 8 columns. Columns 2-8 are the name of a supplier, 'Acme, ZD, RG'
etc. Each row in column 1 has the name of a destination city. I had hoped
that I could use the column heading and row heading to generate a names such
as 'Acme Bakersfield', 'ZD Bakersfield', 'RG Bakersfield', and so on. When I
created the labels, and then linked to a cell, resulting link did appear, at
least on the same worksheet, as "=ZD Bakersfield". However, when I try to
link to that same cell from an external worksheet, I get the absolute
reference, "Sheet1!`$B$6". The problem becomes when I enter a new city, say
Bahran, it should go before Bakersfield, and so my absolute reference is now
incorrect.

I hope I've explained my problem better. Thanks for your help.

"JLatham" wrote:

For the first part of the question, there's an easier way than going through
Insert Name Label. Choose the cell (or cells) to be named and click in the
Name Box (where you see the cell address you've chosen as "A1" or "B99") and
type in the name there. You can also use the pull-down arrow associated with
it to see all the named ranges, click one and you'll be taken to that
location instantly.

As for referencing them in another worksheet in the same workbook, simple:
Assume a cell with name JoeShipperCo on Sheet1, you can reference that cell
simply as =JoeShipperCo anywhere else in the workbook, even as part of a
formula (assume it's a number for a moment) as = 44 + JoeShipperCo

If you want to be specific you can add sheet name to it as
=Sheet1!JoeShipperCo
or
= 44 + Sheet1!JoeShipperCo

If you need to reference it in another workbook, best to start out with the
both workbooks open so Excel can automatically take care of setting path when
you close the linked from workbook. In the second workbook the reference
would be like
=Book1.xls!JoeShipperCo

"Mike C" wrote:

Hello,

I am trying to determine the best way to name cells automatically. I have a
worksheet named Shipper Pricing. It contains the price 7 different suppliers
charge us for delivery to over 70 destinations. As these may change at any
time, new suppliers added, new destinations added or old destinations
deleted, I cannot use absolute references, so must have each cell named.

I tried to use InserNameLabel to create cell reference names for each and
that appeared to work, as long as I was on the same worksheet, however, I
need those referenced names to appear on externally linked worksheets and
can't figure out to do that.

Pleae help. thanks.

To be honest, someone else is going to need to help with the use of
column/row 'headings' as names. It's something I absolutely never do. So I
don't even know whether they will work across worksheets even in the same
workbook (apparently not from what you're saying).

But consider this. Lets say your table starts on Sheet1 at A1 (actually A1
is blank) and you have those entries of 'Acme', 'ZD', 'RG' etc across
starting at B1 out to maybe AA1 (or further, but we'll use AA1)
Then at A2 going down column A you have your list of cities. You've got 54
cities, so that now stretches from A2 down to A55.

A formula on another sheet like this will get you the value of the cell at
the intersection of a particular row/column:
=OFFSET(Sheet1!$A$1,MATCH("Bahran",Sheet1!$A$2:$A$ 55,0),MATCH("ZB",Sheet1!$B$1:$AA$1,0))

If you gave the range A2:A55 on Sheet one a name such as CitiesList and then
gave cells B1:AA1 a name like SupplierList then you could make the formula
look like this:
=OFFSET(Sheet1!$A$1,MATCH("Bahran",CitiesList,0),M ATCH("ZB",SupplierList,0))
and it would work. All you've got to provide different at each cell is city
name and supplier name, and you may even be able to pick those up on the
other sheets? Where I've put "Bahran" and "ZB" into the formulas, you may
use cell references containing the entry to match on.

Also, by having the CitiesList and SupplierList defined, they become dynamic
ranges, so you can add to either one by inserting a new row or column within
either of those named ranges.

Hope that helps some. That should all work even across workbooks, much less
across worksheets.



"Mike C" wrote:

I guess I wasn't clear enough. I don't want to have to name all 490+ cells
individually.

I have 8 columns. Columns 2-8 are the name of a supplier, 'Acme, ZD, RG'
etc. Each row in column 1 has the name of a destination city. I had hoped
that I could use the column heading and row heading to generate a names such
as 'Acme Bakersfield', 'ZD Bakersfield', 'RG Bakersfield', and so on. When I
created the labels, and then linked to a cell, resulting link did appear, at
least on the same worksheet, as "=ZD Bakersfield". However, when I try to
link to that same cell from an external worksheet, I get the absolute
reference, "Sheet1!`$B$6". The problem becomes when I enter a new city, say
Bahran, it should go before Bakersfield, and so my absolute reference is now
incorrect.

I hope I've explained my problem better. Thanks for your help.

"JLatham" wrote:

For the first part of the question, there's an easier way than going through
Insert Name Label. Choose the cell (or cells) to be named and click in the
Name Box (where you see the cell address you've chosen as "A1" or "B99") and
type in the name there. You can also use the pull-down arrow associated with
it to see all the named ranges, click one and you'll be taken to that
location instantly.

As for referencing them in another worksheet in the same workbook, simple:
Assume a cell with name JoeShipperCo on Sheet1, you can reference that cell
simply as =JoeShipperCo anywhere else in the workbook, even as part of a
formula (assume it's a number for a moment) as = 44 + JoeShipperCo

If you want to be specific you can add sheet name to it as
=Sheet1!JoeShipperCo
or
= 44 + Sheet1!JoeShipperCo

If you need to reference it in another workbook, best to start out with the
both workbooks open so Excel can automatically take care of setting path when
you close the linked from workbook. In the second workbook the reference
would be like
=Book1.xls!JoeShipperCo

"Mike C" wrote:

Hello,

I am trying to determine the best way to name cells automatically. I have a
worksheet named Shipper Pricing. It contains the price 7 different suppliers
charge us for delivery to over 70 destinations. As these may change at any
time, new suppliers added, new destinations added or old destinations
deleted, I cannot use absolute references, so must have each cell named.

I tried to use InserNameLabel to create cell reference names for each and
that appeared to work, as long as I was on the same worksheet, however, I
need those referenced names to appear on externally linked worksheets and
can't figure out to do that.

Pleae help. thanks.