copy a changing formula???

[Newbie]

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

 

[Don Guillett]

Yes. Have a look in HELP index for INDIRECT.

 

[Frank Kabel]

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)