Cell Function

[Brenda]

I've tried to post to the Worksheet Function section of
this newsgroup to no avail, so I'll try here. For some
reason, this is the only newsgroup available today.

=IF(B3=K1,C2,VLOOKUP(A3,'C:\Documents and Settings\blc20
\Desktop\[BLAH.xls]CELL("CONTENTS",L2)'!B1:U50,20,FALSE))

This cell function does not work. I'm trying to return
the value to replace the sheet name here so that as the
months change, the formula will automatically update to
the current month.

Any ideas?

TIA,
Brenda

 

[Bill Manville]

=IF(B3=K1,C2,VLOOKUP(A3,'C:\Documents and Settings\blc20
\Desktop\[BLAH.xls]CELL("CONTENTS",L2)'!B1:U50,20,FALSE))

Unfortunately you can't change the sheet name in this way.
If BLAH.xls was open you could use the INDIRECT function

=IF(B3=K1,C2,VLOOKUP(A3,INDIRECT("'[BLAH.xls]" & CELL("CONTENTS",L2) &
"'!B1:U50"),20,FALSE))

But if you want the formula to work when BLAH.xls is not open you will
need to modify the formula each month.

I would define a range name MonthTable
='C:\Documents and Settings\blc20
\Desktop\[BLAH.xls]Apr2005'!B1:U50

and in your formula use
=IF(B3=K1,C2,VLOOKUP(A3,MonthTable,20,FALSE))

Then in Workbook_Open have
ThisWorkbook.Names("MonthTable").RefersTo = "='C:\Documents and
Settings\blc20\Desktop\[BLAH.xls]" & format(Date,"mmmyyyy") &
"'!B1:U50"

Bill Manville
MVP - Microsoft Excel, Oxford, England