How do you reference a defined range (dynamic) on another sheet?
This doesn't work and several other variations I have tried. . . [
I]=LOOKUP(Sheetname!A1, RangeName) [/i]


--
kermitforney
------------------------------------------------------------------------
kermitforney's Profile: http://www.excelforum.com/member.php...o&userid=32536
View this thread: http://www.excelforum.com/showthread...hreadid=523313

kermitforney wrote...
How do you reference a defined range (dynamic) on another sheet?
This doesn't work and several other variations I have tried. . .
=LOOKUP(Sheetname!A1, RangeName)

How is RangeName defined? How does the formula above not work? Does it
return an error value, the wrong answer, or does Excel not even allow
you to enter it?

Harlan Grove Wrote:
kermitforney wrote...
How do you reference a defined range (dynamic) on another sheet?
This doesn't work and several other variations I have tried. . .
=LOOKUP(Sheetname!A1, RangeName)

How is RangeName defined? How does the formula above not work? Does it
return an error value, the wrong answer, or does Excel not even allow
you to enter it?

Here is an example =LOOKUP(formulas!A2, Plan)

I get the #N/A error 'Value Not Available'


--
kermitforney
------------------------------------------------------------------------
kermitforney's Profile: http://www.excelforum.com/member.php...o&userid=32536
View this thread: http://www.excelforum.com/showthread...hreadid=523313

any suggestions?


--
kermitforney
------------------------------------------------------------------------
kermitforney's Profile: http://www.excelforum.com/member.php...o&userid=32536
View this thread: http://www.excelforum.com/showthread...hreadid=523313

any suggestions?


--
kermitforney
------------------------------------------------------------------------
kermitforney's Profile: http://www.excelforum.com/member.php...o&userid=32536
View this thread: http://www.excelforum.com/showthread...hreadid=523313

kermitforney wrote...
Harlan Grove Wrote:
....
How is RangeName defined? How does the formula above not work? Does it
return an error value, the wrong answer, or does Excel not even allow
you to enter it?

Here is an example =LOOKUP(formulas!A2, Plan)

I get the #N/A error 'Value Not Available'

You still haven't mentioned how Plan is defined. But I'm going to guess
that either there are stray trailing space characters either in
formulas!A2 or in the first column of Plan or the first columns of Plan
isn't sorted in ascending order or the first column of Plan contains a
mix of text and numbers. Any of these would be difficult to detect and
could cause LOOKUP to return #N/A.

Thanks for all the help but I just switched it from a dynamic range to a
static range and it works just fine now. :( oh well


--
kermitforney
------------------------------------------------------------------------
kermitforney's Profile: http://www.excelforum.com/member.php...o&userid=32536
View this thread: http://www.excelforum.com/showthread...hreadid=523313