Named Ranges and Formulas

Hi All,

I know how to created named ranges etc.

What I am curious about for example is whether it is possible to have a name within a cell and use the name from that cell to a named range?

ie

test = Sheet1!$A$1:$C$5

therefore

vlookup(d1,test,3,false)

but if cell d2 contained the word 'test' in the cell is there a way to make this work:

contents of cell d2 = test
vlookup(d1,d2,3,false)

Now as the formula stands it returns a N/A.

Is there a way that if can use the result of cell d2 to look at the range called test?

I think I know the answer already is that it can't be done but I thought I would throw it out there and see if there is a way...

vaneagle

Hi

Try this.

=VLOOKUP(D1,INDIRECT(D2),3,FALSE)

rylo

Originally Posted by rylo

Hi

Try this.

=VLOOKUP(D1,INDIRECT(D2),3,FALSE)

rylo

Thanks rylo.

oic...

If the named range is singular, ie a cell is named test for example then it seems to work, but if the named range is say $A$1:$C$5 is comes up with a #value

Does this make sense? So it appears to work for single cell ranges but not when the range refers to multiple cells and columns...

hmm any other ideas?

Hi

No it should work for a multicell range.

So if A1:C5 was a named range called test, a3 has the word test, c3 has say 22, D1 has test, D2 has test the formula should return 22. If you can't get it to work, then put up your example file for review.


rylo

Originally Posted by rylo

Hi

No it should work for a multicell range.

So if A1:C5 was a named range called test, a3 has the word test, c3 has say 22, D1 has test, D2 has test the formula should return 22. If you can't get it to work, then put up your example file for review.


rylo

good idea!

I have attached for others for just in case...

I think i understand it better now...

Thanks for your help...

I need to reveiw the file I wish to apply this too...

vaneagle: Second request that you not quote whole posts. There's no need to clutter the forum with duplication.