I have a spreadsheet with various data in different sheets.
I have the following formula =vlookup(modelcode,C52,3) whereby cell C52
contains the name of the sheet to lookup the data. This name is not constant
as various sheet names, e.g. PRIVA, TACTI, CSMAF etc. contain the lookup
data. By using the above formula, it returns the #N/A error. If I substitue
the cell name with the actual sheet name in the formula. e.g. PRIVA, the
formula returns the correct answer. As the sheet name in cell C52 varies, is
it possible for the formula to pick the name of the lookup sheet from cell
C52, rather than me having to enter the actual sheet name every time.

Thanks in advance for any suggestions.

John

I don't follow you completely but here is how I see your issue. The second
argument of the VLookup function (where you have C52) takes a range of cells
(obviously C52 is a single cell not a range). What you want to do is to take
the value in Cell C52 which is the address for a range of Cells and use that
range in your formula. To do that you probably want to use the Indirect
function, which takes the address for a range of cells and returns the actual
range... So it would be something like this...

=vlookup(modelcode,indirect(C52),3)

--
HTH...

Jim Thomlinson


"John Davies" wrote:

I have a spreadsheet with various data in different sheets.
I have the following formula =vlookup(modelcode,C52,3) whereby cell C52
contains the name of the sheet to lookup the data. This name is not constant
as various sheet names, e.g. PRIVA, TACTI, CSMAF etc. contain the lookup
data. By using the above formula, it returns the #N/A error. If I substitue
the cell name with the actual sheet name in the formula. e.g. PRIVA, the
formula returns the correct answer. As the sheet name in cell C52 varies, is
it possible for the formula to pick the name of the lookup sheet from cell
C52, rather than me having to enter the actual sheet name every time.

Thanks in advance for any suggestions.

John