Hello,
Is there a way to retrieve range name if value is in the range?
For example I got this column wit names:
name 1
name 2
name 3
name 4
name 5
name 6
name 7
name 8
name 9
name 10
name 11
name 12
This column has name ranges:
1st range called €œCA€� has these names:
name 1
name 2
name 3
name 4
2nd range called €œDC€� has these names:
name 5
name 6
name 7
name 8
name 9
And the 3rd range is called €œWA€�:
name 10
name 11
name 12
Lets say in different worksheet my formula returned €œname1, name8 and name
11€�, in the same worksheet I would like to return the location or range name
associated with name.
Desired result:
__A_______B__
name 1___CA__
name 8___DC__
name 11__WA__
I think with nested IF functions I could achieve desired result; problem is
I am exceeding the limit of allowed nested functions. And I also dont want
to add additional column in original data sheet were I specify the
location/range name for each name.
Any suggestions?
Thank you,
Guntars

Why use range names?
On the first sheet use
name 1 CA
name 2 CA
name 3 CA
name 4 CA
name 5 DC
etc
with the names in say column A and the codes in column B

On the other sheet
A1 B1
name 1 =VLOOKUP(A1,Sheet1!A1:B1000,2,FALSE)

if name 1 is found in column A the formula will return the code from the B
column

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Guntars" wrote in message
...
Hello,
Is there a way to retrieve range name if value is in the range?
For example I got this column wit names:
name 1
name 2
name 3
name 4
name 5
name 6
name 7
name 8
name 9
name 10
name 11
name 12
This column has name ranges:
1st range called €œCA€� has these names:
name 1
name 2
name 3
name 4
2nd range called €œDC€� has these names:
name 5
name 6
name 7
name 8
name 9
And the 3rd range is called €œWA€�:
name 10
name 11
name 12
Lets say in different worksheet my formula returned €œname1, name8 and name
11€�, in the same worksheet I would like to return the location or range
name
associated with name.
Desired result:
__A_______B__
name 1___CA__
name 8___DC__
name 11__WA__
I think with nested IF functions I could achieve desired result; problem
is
I am exceeding the limit of allowed nested functions. And I also dont
want
to add additional column in original data sheet were I specify the
location/range name for each name.
Any suggestions?
Thank you,
Guntars

Hello,

something like that ?:

with a function in a module,

Function NameIS(xR) As String
NameIS = "No Name"
For Each Xname In ActiveWorkbook.Names
On Error GoTo Nextname
Set RR = Range(Mid(Xname.RefersTo, 2, 99))
For Each Xcell In RR
If Xcell.Value = xR.Value Then
NameIS = Xname.Name
Exit For
End If
Next Xcell
Nextname:
If NameIS < "No Name" Then Exit For
Next Xname
End Function



Example

If cell C1 contains the string 'name 9'
if the formula in Cell D1 is '=NameIS(C1)'
The result in D1 shoud be 'DC'






"Guntars" a écrit dans le message de
...
Hello,
Is there a way to retrieve range name if value is in the range?
For example I got this column wit names:
name 1
name 2
name 3
name 4
name 5
name 6
name 7
name 8
name 9
name 10
name 11
name 12
This column has name ranges:
1st range called €œCA€� has these names:
name 1
name 2
name 3
name 4
2nd range called €œDC€� has these names:
name 5
name 6
name 7
name 8
name 9
And the 3rd range is called €œWA€�:
name 10
name 11
name 12
Lets say in different worksheet my formula returned €œname1, name8 and name
11€�, in the same worksheet I would like to return the location or range
name
associated with name.
Desired result:
__A_______B__
name 1___CA__
name 8___DC__
name 11__WA__
I think with nested IF functions I could achieve desired result; problem
is
I am exceeding the limit of allowed nested functions. And I also dont
want
to add additional column in original data sheet were I specify the
location/range name for each name.
Any suggestions?
Thank you,
Guntars

Hi,

I prefer Bernard's approach but here is a method you could use which matches
the question as you asked it:

=IF(OR(CA=E1),"CA",IF(OR(DC=E1),"DC",IF(OR(WA=E1), "WA")))

This formula assumes that the text you want to search for is in cell E1.
This is an array entered formula - Press Shift+Ctrl+Enter to enter it.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Guntars" wrote:

Hello,
Is there a way to retrieve range name if value is in the range?
For example I got this column wit names:
name 1
name 2
name 3
name 4
name 5
name 6
name 7
name 8
name 9
name 10
name 11
name 12
This column has name ranges:
1st range called €œCA€� has these names:
name 1
name 2
name 3
name 4
2nd range called €œDC€� has these names:
name 5
name 6
name 7
name 8
name 9
And the 3rd range is called €œWA€�:
name 10
name 11
name 12
Lets say in different worksheet my formula returned €œname1, name8 and name
11€�, in the same worksheet I would like to return the location or range name
associated with name.
Desired result:
__A_______B__
name 1___CA__
name 8___DC__
name 11__WA__
I think with nested IF functions I could achieve desired result; problem is
I am exceeding the limit of allowed nested functions. And I also dont want
to add additional column in original data sheet were I specify the
location/range name for each name.
Any suggestions?
Thank you,
Guntars