MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Duplicate Range names


Posted by I must going brain-dead on May 08, 2001 9:01 PM

hope someone can help me with this one :

*Let's say you have a file with just one sheet (Sheet1)
that has a range name called "TEST" (sheet1!$A$1:$A$10)

*Now you make a copy of Sheet1 & rename it Sheet2

*Now you have 2 tabs each having a defined range "TEST"
* TEST = Sheet1!$A$1:$A$10
* TEST = Sheet2!$A$1:$A$10
{What I refer to as worksheet level range names}

*Here's the dilemma, how can you generate a list of all the
range names - one that can identifies "TEST" as being
on both Sheet1 & Sheet2. I've tried the insert\range
paste\paste list. Doesn't show both ranges.

Any suggestions would be appreciated
Thxs


Posted by Mark W. on May 08, 2001 9:53 PM

Actually, what you have is 1 globally defined name,
TEST, that refers to Sheet1!$A$1:$A$10 and 1
locally defined name Sheet2!TEST that refers to
Sheet2!$A$1:$A$10.

Someone must have create a utility to help you
list a workbook's defined names, but I don't
know where to steer you.

You could Insert a new worksheet, enter Sheet1!TEST
into cell A1, enter Sheet2!TEST into cell A2, and
the toggle the display to formulas using Ctrl+~
(Control+Tilde). Any names prefaced by the
workbook name (e.g., Book1) is a globally defined
name.

Posted by Right Behind you on May 08, 2001 9:53 PM

Oh No, insanity, its contageous

Dear Dane Bread:

I was able to duplicate your scenario and also able to determine a formula to total both TESTs.

As far as listing them, boy, that is a stumper.

From: Brains! I thought they said Pains and asked for none.

Posted by Kevin James on May 08, 2001 9:59 PM

Hi Mark,

After duplicating Dear Brain Dead's scenario, on a third sheet, I was able to write the formula =sum(test).

Now, I put 10 in A1 & A2 on the first sheet and put 30 on the second sheet. The result of the formula I wrote was, as you mentioned, the result of the "global" name occuring on the first sheet.

Interesting, but (as they used to say on "LaughIn") "stupid." It seems Micro Soft-in-the-brains should have prevented duplicate names like that.

Kevin

Posted by Mark W. on May 08, 2001 10:25 PM

While this convention seems a bit strange (and awkward) at first, it can be quite useful. Think of it as the spreadsheet equivalent of modular code that supports locally defined variables.

Posted by Brain-Dead on May 09, 2001 6:17 AM

Thxs for all the input - I was afraid this one might
be a stumper.

again thxs for the help
BD


While this convention seems a bit strange (and awkward) at first, it can be quite useful. Think of it as the spreadsheet equivalent of modular code that supports locally defined variables. : Hi Mark, : After duplicating Dear Brain Dead's scenario, on a third sheet, I was able to write the formula =sum(test). : Now, I put 10 in A1 & A2 on the first sheet and put 30 on the second sheet. The result of the formula I wrote was, as you mentioned, the result of the "global" name occuring on the first sheet. : Interesting, but (as they used to say on "LaughIn") "stupid." It seems Micro Soft-in-the-brains should have prevented duplicate names like that. : Kevin

Posted by Brain - Dead on May 09, 2001 6:20 AM

Thxs for all the input. I was afraid this one might
be a stumper.

Again thxs for the help.
BD

While this convention seems a bit strange (and awkward) at first, it can be quite useful. Think of it as the spreadsheet equivalent of modular code that supports locally defined variables. : Hi Mark, : After duplicating Dear Brain Dead's scenario, on a third sheet, I was able to write the formula =sum(test). : Now, I put 10 in A1 & A2 on the first sheet and put 30 on the second sheet. The result of the formula I wrote was, as you mentioned, the result of the "global" name occuring on the first sheet. : Interesting, but (as they used to say on "LaughIn") "stupid." It seems Micro Soft-in-the-brains should have prevented duplicate names like that. : Kevin

Posted by Brain-Dead on May 09, 2001 6:21 AM

Thxs for all the input. I was afraid this one might
be a stumper.

Again thxs for the help.
BD

Posted by Brain-DEAD on May 09, 2001 6:24 AM

Thxs for all the input. I was afraid this one might
be a stumper.

Again thxs for the help.
BD

Posted by Dave Hawley on May 09, 2001 8:13 AM

A simple loop will do this...

Hi BD

Use this macro:

Sub ListAllNames()
'Wriiten by OzGrid Business Applications
'www.ozgrid.com

Dim nNames As Name
Dim i As Integer

i = 1
For Each nNames In ActiveWorkbook.Names
Cells(i, 1) = nNames.Name
Cells(i, 2) = nNames.Parent.Name
Cells(i, 3) = nNames.RefersToRange.Address
i = 1 + 1
Next

End Sub

Dave
OzGrid Business Applications

Posted by BD on May 09, 2001 8:53 PM

THXS !!!!

David :
Thxs for the all the help. The macro works !!
It's great especially if you make a minor revision
change "i = 1 + 1" to:
"i = i + 1"

again thxs for the help
BD Hi BD Use this macro: Sub ListAllNames()