same range name, multiple sheets

  • Thread starter Thread starter Tracey
  • Start date Start date
T

Tracey

For simplicity purposes, I want to use the same range name
on multiple worksheets referencing individual cells in
each workbook.

For example, LastRow=A26 for the particular worksheet that
is currently active. I've been able to do this in one
worksheet but for some reason, two other worksheets appear
to be linked and whenever I change LastRow in one, it also
changes in the other.
 
Defne the name LOCALLY by giving it the name preceded by the spreadsheet.
For example:
Name is Sheet1!LastRow and refers to is =$A$26.
On another sheet, like xyz sheet:
Name is 'xyz sheet'!LastRow and refers to is =$A$44 (or whatever it is)

Bob Umlas
Excel MVP
 
Quicker to select the target cell(s), and type the name in the names box,
with the sheet prefix as Bob describes.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Tracey said:
For simplicity purposes, I want to use the same range name
on multiple worksheets referencing individual cells in
each workbook.
Click to expand...
....

In addition to the worksheet-level names others have already mentioned there
are two other alternatives. You can define workbook-level names that include
only an exclamation point, e.g., RefersTo is =!$A$26. This can lead to
trouble in some situations. The other alternative is using INDIRECT, e.g.,
RefersTo is =INDIRECT("A26"). This won't change no matter how you insert or
delete rows/columns or even cut-and-paste onto A26 in any worksheet - this
has its pluses and minuses. Both will resolve to references to cell A26 on
the worksheet containing the formula refering to either.
 
Back
Top