i really have no idea if this can be done...

B

BigAl

in the below formula, can i relate "Sheet1" to a value in a cell. for
example, if i have a load of work sheets (named a, b c, etc. for
convenience) could i put a,b,c etc. in a column and relate the formula
to those values?

=IF(C6="elective",VLOOKUP(Sheet1!B6,elective,2,FALSE),IF(C6="emergency",VLOOKUP(Sheet1!B6,emergency,2,FALSE),VLOOKUP(Sheet1!B6,daycase,2,FALSE)))*1.0541
 
P

Paul

BigAl said:
in the below formula, can i relate "Sheet1" to a value in a cell. for
example, if i have a load of work sheets (named a, b c, etc. for
convenience) could i put a,b,c etc. in a column and relate the formula
to those values?
Click to expand...
=IF(C6="elective",VLOOKUP(Sheet1!B6,elective,2,FALSE),IF(C6="emergency",VLOO
KUP(Sheet1!B6,emergency,2,FALSE),VLOOKUP(Sheet1!B6,daycase,2,FALSE)))*1.0541

You can use the INDIRECT function within a formula to convert a text string
into a cell or range reference, for example like this:
VLOOKUP(INDIRECT(D1&"!B6"),elective,2,FALSE)
where D1 contains the text "Sheet1".

However, be aware that this forces the "!B6" part to be entered as text (as
shown, between double quotes). Therefore it will no longer adjust if the
formula is copied or moved, or if rows or columns are inserted or deleted. I
consider this to be a major drawback that I avoid if possible, but that's
for you to decide. One possible alternative approach is to have separate
VLOOKUP formulas that bring data from all the different sheets into one row,
and then simply select from the appropriate column of this row according to
the required sheet name.
 
Top