I have looked at it, and the approach I proposed earlier wouldn't work here,
you are right.
My proposal for the note in B15 is changing the formulas in C15:C18, to
avoid checking for the value in B1, but directly for the ranges. Something
like:
C15:
=IF(ISNA(VLOOKUP(A15,week1:Data!E1,5,FALSE)),VLOOK UP(A15,el:Data!F15,5,FALSE),VLOOKUP(A15,week1:Data !E1,5,FALSE))
C16:
=IF(ISNA(VLOOKUP(A16,week1:Data!E2,5,FALSE)),VLOOK UP(A16,el:Data!F16,5,FALSE),VLOOKUP(A16,week1:Data !E2,5,FALSE))
C17:
=IF(ISNA(VLOOKUP(A17,mr:Data!G17,5,FALSE)),VLOOKUP (A17,sz:Data!H12,5,FALSE),VLOOKUP(A17,mr:Data!G17, 5,FALSE))
C18:
=IF(ISNA(VLOOKUP(A18,mr:Data!G18,5,FALSE)),VLOOKUP (A18,sz:Data!H13,5,FALSE),VLOOKUP(A18,mr:Data!G18, 5,FALSE))

The formulas in C15 and C16 look for data in the "ad" and "el" options, and
C17 and C18 look for "mr" and "sz". They are not directly related to B1, so
I don't know if this will be a valid solution for you.

Miguel.

"dpatte601" wrote:

Here is the worksheet , Maybe you can understand what i want if you see it
at work
http://www.savefile.com/files/8153879

Hi,

I don't know if I have understood correctly, but I have faced similar
situations and this an approach that worked for me:
1. Name the ranges where the list values are, including all the rows
and columns that will be searched using VLOOKUP (can be the same as
you have now, TB, EL, SZ, MR)
2. In the cell $B$1, or wherever you are selecting the code, make the
dropdown with the names that you have created in point 1
3. Use the INDIRECT in the VLOOKUP formula, something like this:
=VLOOKUP(A5,INDIRECT($B$1),5,FALSE)
The formula will search the named range that is represented in that
cell.

Hope this helps,
Miguel.