Use same name for ranges on different sheets

  • Thread starter Thread starter James
  • Start date Start date
J

James

After copying a sheet with named ranges, I have the same
names for different ranges on multiple sheets.

As a simplified example, after coping a sheet "January"
with the range "Schedule", and renaming the copied sheet
to "February", and adjusting the "Schedule", I can have
two sheets with different ranges but the same name.

Therefore, I can have one formula that
references "Schedule" and copy it to any sheet, and it
will reference the range on that sheet. This is better
than having many ranges named "Jan_Sched", "Feb_Sched",
etc., and having a different formula on each sheet.

How can I duplicate this feat after all of my sheets have
already been created and I want to name some new ranges?
Is is possible? I haven't been able to find a way.
Thanks for any help.
 
James said:
After copying a sheet with named ranges, I have the same
names for different ranges on multiple sheets.

As a simplified example, after coping a sheet "January"
with the range "Schedule", and renaming the copied sheet
to "February", and adjusting the "Schedule", I can have
two sheets with different ranges but the same name.

Therefore, I can have one formula that
references "Schedule" and copy it to any sheet, and it
will reference the range on that sheet. This is better
than having many ranges named "Jan_Sched", "Feb_Sched",
etc., and having a different formula on each sheet.

How can I duplicate this feat after all of my sheets have
already been created and I want to name some new ranges?
Is is possible? I haven't been able to find a way.
Thanks for any help.
Click to expand...

When you define the name by selecting the cell (or range) and using
Insert > Name > Define ,
Excel offers the range definition with the sheet name included, for example
=Sheet1!$A$1
or
=Sheet1!$A$1:$A$9
This defines a workbook-level name.

However, if you delete the sheet name (but leaving the "!"), that is
=!$A$1
or
=!$A$1:$A$9
this will define a worksheet-level name as you require.
 
When you define the name by selecting the cell (or range)
and using
Insert > Name > Define ,
Excel offers the range definition with the sheet name included, for example
=Sheet1!$A$1
or
=Sheet1!$A$1:$A$9
This defines a workbook-level name.

However, if you delete the sheet name (but leaving the "!"), that is
=!$A$1
or
=!$A$1:$A$9
this will define a worksheet-level name as you require.
Click to expand...

Thanks Paul, but this doesn't seem to do the trick. It
creates an absolute reference, so that if the position of
the range is changed by inseting a row, it is no longer
linked correctly. This does not occur when using normal
ranges. Any other ideas?
 
James said:
Thanks Paul, but this doesn't seem to do the trick. It
creates an absolute reference, so that if the position of
the range is changed by inseting a row, it is no longer
linked correctly. This does not occur when using normal
ranges. Any other ideas?
Click to expand...

Paul gave you the most appropriate answer based on the apparently too skimpy
information in your original post. So here's an idea: TELL US HOW SCHEDULE
IS DEFINED IN THE JANUARY WORKSHEET. Then maybe someone can give you some
assistance more relevant to your needs.
 
As requested by Harlan, here are some more details.

Actually, the range "Schedule" is alright, because I had
named a range in my original sheet, and had progressivly
copied it as I created new sheets for each new accounting
period (AP), since I'm not actualy using months. So,
in "AP 1", "Schedule"='AP 1'!$G$2:$AI$7

After copying that sheet as "AP 3" (which contains 35 days
as opposed to 28 in AP 1 and AP 2), and adjusting it as
necessary, the range "Schedule"='AP 3'!$G$2:$AP$7

This is what I need for other ranges. The same name (just
as I have for "Schedule"), but different physical
locations on each sheet (just as $G$2:$AI$7 is different
than $G$2:$AP$7).

I'm creating formulas where I try looking up an employee's
number in one of seven different workbooks, and when it
finds a workbook where he is located, it will return a
value (if there is one) that is located under the same day
of the same accounting period. Therefore, I would like to
use a range for employee numbers and another for dates.

A complex series of IF statements are required, and the
formula ends up being too long to enter normaly.
Therefore, I'm creating named formulas, and using them
with a series of IF statements. I have to create one
formula for searching in each of the seven workbooks,
times two, because each AP overlaps two months, and the
other seven workbooks are broken up into months. That is
14 formulas for searching, and then I need 14 more to
return the value if one is found.

28 formulas would be fine, but because I have to use
different range names in each sheet, I can't have just 28
formuals. I need 28 per each of thirteen different
accounting periods, and it is taking too long, and it is
going to be too difficult to troubleshoot. The ranges
would need to be relative, because it is possible that
additional rows will be added above list of employees.

Can anyone help? Just as a reminder, all I want to do is
find a way to use the same name for different ranges on
different sheets. It can be done if you copy the sheet,
but since I've already put a lot of work into each of the
sheets, I'm trying to find another way to do it, after the
fact. Thank you.
 
Maybe I'm missing something here. All you need to do is select the range and
then use:

Insert | Name | Define

and type in the name you want to give the range prefixed by the sheet name
and an exclamation point into the *top* text box; e.g.:

Sheet1!MyRange
 
-----Original Message-----
Maybe I'm missing something here. All you need to do is select the range and
then use:

Insert | Name | Define

and type in the name you want to give the range prefixed by the sheet name
and an exclamation point into the *top* text box; e.g.:

Sheet1!MyRange
Click to expand...

Vasant, I want to be able to use the same range names on
each sheet. If I follow your advise, when I use that name
to define a range on one sheet, it will no longer define
any range on another sheet. Thank you.

For example, try creating two different print areas on two
sheets. Go to Insert - Name - Define. On one sheet you
may see the named range:
Print_Area 'Sheet1'
With a definition such as: ='Sheet1'!$A$1:$B$2
Now go to the other sheet, and then open Insert - Name -
Define. Now you may see the named range:
Print_Area 'Sheet2'
With a definition such as: ='Sheet2'!$C$3:$D$4

Both ranges are called Print_Area. They both define
different areas on different sheets. You cannot see them
both when you go to Insert - Name - Define, only the one
on the page you have open. And, they are relative
references, so if you were to add columns and rows, the
print area would change accordingly.

Any other ideas of how to replicate this feat?
 
James, did you try my suggestion and follow it exactly? It will do precisely
what you seem to require.
 
and type in the name you want to give the range
prefixed
Thank you Vasant. I saw that the sheet name was in the
definition box at the bottom, but I did not actually type
it into the name box at the top. After re-reading your
instructions this does seem to work. Thanks again.
 
Note that if you do insert>name>define and select the range with the mouse
or just type the name into the name box
it will default to the sheet names
 
Hi James,

I was running into the same problem. I don't know how comfortable yo
are with macros or VBA but I found some code that will do what you ar
asking.

Sub NameMaker()
''''only works with simple worksheet names
For Each wks In ActiveWorkbook.Worksheets
wks.Range("B9").Name = wks.Name & "!Data"
Next wks
End Sub


It creates the same range "Data" on each sheet in the workbook. Yo
can then move or change the range on each sheet separately.

I never did figure out how to do this otherwise.

Hope this helps
 
Back
Top