May as well bump this thread .... trying to figure out how to have a variable sheet name in a formula, I'm trying to pull data from three sheets onto one, for example on one sheet I have this formula in C8:

=VLOOKUP($A8,BS!$A$7:$H$107,C$1,FALSE)

A8 contains the value I'm doing a vlookup on obviously.

What I want is a means of the sheetname table array value to be variable (It's also the first two characters of the lookup_value, so want to use =Left($A8,2) in there as well)

Can reference different workbooks, just struggling with different sheets...

Any advice please?