=LOOKUP on another Sheet

K

kermitforney

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
 
H

Harlan Grove

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)
Click to expand...

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?
 
K

kermitforney

Harlan said:
kermitforney 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?
Click to expand...

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

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

Harlan Grove

kermitforney wrote...
Harlan Grove Wrote: ....

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

I get the #N/A error 'Value Not Available'
Click to expand...

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.
 
K

kermitforney

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