Excel: Look for different worksheet based on date.

[vjh]

Hi all,

I have a workbook that collects information on a monthly basis - a separate one for each month, based on a template I've developed.

For some biweekly reports, I need to extract data from the previous month's worksheet if the date falls outside the current month.

How do I tell it to go to say JanReports.xls if it's February, or MarchReports.xls if it's April. I'd like to have it part of the template, and not manually re-enter it each month.

I've attempted to have a named range where the previous month's filename is typed in, but can't seem to get this to work.

Any suggestions?

Thanks!

vjh

 

[AnotherHiggins]

To get the previous month's data, you could use something like:
[COLOR=blue white]=INDIRECT("C:\path\"&TEXT(TODAY()-DAY(NOW()),"mmm")&"Reports.xls")[/color]

One caveat with that: It remains dynamic - that is to say that right now it will lead to 'OctReports.xls' but as of Dec 1 it will return 'NovReports.xls'. If you want the data to remain after month's end, you'll need to copy the selection that uses the formula, then Paste Special > Values.



[tt]__________________________________________
My name is John, and I approved this post.[/tt]

To get the best answers fast, please read faq181-2886

 

[vjh]

Thanks.

I've been struggling with using INDIRECT to get to the right named range of the right named worksheet of the right workbook, and embed that into a fairly complex INDEX / MATCH() / MATCH statement. Which I've done.

Phew!!!

vjh