thanks, i just worked this out what I done wrong about a minute before got the subscription email telling me you replied. thanks for all your help



the formula that seems to work i settled with is

I realised that the indirect wasnt closed properly.

I'm still wondering what part the & sign plays on the BK2 reference. I've never seen it before? what does that do when I use it? or is it something purely for indirect?

The ampersand (&) joins the expression preceding it with the one following it. With:
"'[2015 Cordell Valuation Lookup Tables.xlsx]"&BK2&"'!$A:$I"
there are thus three expressions being joined:
• "'[2015 Cordell Valuation Lookup Tables.xlsx]"
• BK2
• "'!$A:$I"
The first and last of these are literal strings; the second one retrieves the contents of BK2. So, if BK2 contains 'Data type 1', you end up with a VLOOKUP formula referencing:
"'[2015 Cordell Valuation Lookup Tables.xlsx]Data type 1'!$A:$I"

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

thanks, was amazed i couldnt find anything that mentioned how the & worked when googling, just loads about the old Dollar sign! $$ thanks again. my sheet is going to do its own work when i finish this macro.