Thread: Getting data from previous months

I worksheets made for each month but I require the current sheet to always acquire the worksheet of the previous month. how to I go about doing it?Eg.

"Dec09" to get data from "Nov09" and "Nov09" to get data from "Oct09"

one way
=INDIRECT(TEXT(EOMONTH(TODAY(),-1),"mmmyy")&"!A1") but it would probably be better to put =today() in a spare cell and reference that
=INDIRECT(TEXT(EOMONTH(B1,-1),"mmmyy")&"!A1") where B1 =today()
you need to activate the analysis tool pack to use eomonth function

I actually use a UDF for this, also then in conjunction with an INDIRECT() formula. Here's the UDF:

Function Previous()
'Returns the name of the previous sheet as   =Previous()
    Previous = Worksheets(Application.Caller.Parent.Index - 1).Name
End Function

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.

========
On sheet2, you would normally put this in to get cell A1 from the prior sheet:
=Sheet1!A1

Now you can use this instead:
=INDIRECT("'" & PREVIOUS() & "'!A1")

This formula would always grab cell A1 from the previous sheet, whatever it is called.

On the off chance you have morefunc.xll installed see also SHEETOFFSET function, like JB's UDF this assumes you are are listing your sheets in sequential order - if not then you need to use Martin's approach.

Beware of course that INDIRECT is Volatile (see link in sig. for more info)