Hi
if the sheet name is in column B and the last row for this range in
column C try
=VLOOKUP(A1,INDIRECT"'" & B1 & "'!$A$2:$I$" & C1),9,FALSE)


--
Regards
Frank Kabel
Frankfurt, Germany


Newbie wrote:
Hi,
I have the following formula
=VLOOKUP(A1,Jan2004'!$A$2:$I$235,9,FALSE)

However, each month the worksheet changes (Feb2004, Mar2004 etc) and
the range could be more or less
is there a way to build this formula in code and replace the
sheetname and range with variables and then paste and copy this down
in to a column on another sheet where the column heading is the same
as the sheet name?

ie. Sheet where the formula appears is laid out like so

Code(lookup value) Jan Feb Mar Apr etc

appropriate data needs to be input into the correct column based on
the sheet name i.e Jan2004, Feb2004 Mar2004

Thanks