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

Yes. Have a look in HELP index for INDIRECT.


--
Don Guillett
SalesAid Software

"Newbie" wrote in message
...
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

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