VLOOKUP ~ Dynamic

[asking]

Hi all,

Using excel 2007 on Vista

I have this formula which works ~

=VLOOKUP(C11,[Compound_2011.xlsm]Easy!$E$1894:$Z$1992,22)

However the Easy range changes weekly.

My problem is if I filter The sheet Easy by date and use an offset named range to select the same range as above, then put that named range in the formula, instead of $E$1894:$Z$1992 as in ~

=VLOOKUP(C11,[Compound_2011.xlsm]Easy!Kludo,22)
Kludo is the named range for ~ Easy!$E$1894:$Z$1992

It won't work with any type of named range only the typed range ~ $E$1894:$Z$1992

Any way to make VLOOKUP dynamic? Or am I doing something wrong with the formula?

Cheers.

 

[nightcrawler23]

I believe named ranges are limited to the same workbook only.

 

[asking]

Thanks for the reply.

Something more I've learned.
It is a pain to open the other workbook, find the range, and return to enter the range. Thought a formula would suffice.
I will have to make do with some VBA coding to accomplish the task.

Thanks once again.

Cheers