Formula to look at sheets with changing names

Hi,

I have a file where I add a worksheet each month, named as the current month, e.g. Jun15.

What I'm wanting to do is have a sheet that calculates movements from the latest month and previous month. So this month, my formula would look at Jun15 and May15 worksheets. However, once we get to July and I add a new worksheet, I want my formula to look on sheets Jul15 and Jun15.

I can't work out how to do this. I know that sheets can be reffered to by number in VBA, but I don't know how I'd set that up either, so I'm at a bit of a loss on how to do this. I mention this as the new month is always the foremost sheet, so the latest would be sheet 1, previous month sheet 2 etc.

Many thanks

Could you upload a sanitised sample workbook.
Include any relevant sheets.

Windy

Could you upload a sanitised sample workbook.
Include any relevant sheets.

Windy

You can use INDIRECT to do this, along the lines of:

=INDIRECT("'"&TEXT(TODAY(),"mmm-yy")&"'!B10")-INDIRECT("'"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"mmm-yy")&"'!B10")

This will look at B10 in the current month's sheet and subtract from it the contents of B10 in the previous month's sheet.

Hope this helps.

Pete

Here's a really simple example.

I've manually made the formulas on the movement sheet, but I want to avoid changing them next month when I add a Jul15 sheet.

Example.xlsx

Assuming your data is always in the same layout on those monthly sheets (i.e. Dogs is always on row 2), then it would be a bit more flexible if you entered the date of the current month in D1 rather than rely on the TODAY() function, as you might not create the July sheet until, say, 3rd July. So, in your example put 1/06/2015 in D1, then you can have this formula in D2:

=INDIRECT("'"&TEXT(D$1,"mmmyy")&"'!B"&ROWS($1:2))-INDIRECT("'"&TEXT(D$1-1,"mmmyy")&"'!B"&ROWS($1:2))

which can be copied into D3. Then when you have created a sheet for July, just change the date in D1 to 1/07/2015 - this also makes it more obvious which months you are comparing.

Hope this helps.

Pete

Perfect, that's exactly what I was trying to get to

Thank you very much!

Originally Posted by Pete_UK

Assuming your data is always in the same layout on those monthly sheets (i.e. Dogs is always on row 2), then it would be a bit more flexible if you entered the date of the current month in D1 rather than rely on the TODAY() function, as you might not create the July sheet until, say, 3rd July. So, in your example put 1/06/2015 in D1, then you can have this formula in D2:

=INDIRECT("'"&TEXT(D$1,"mmmyy")&"'!B"&ROWS($1:2))-INDIRECT("'"&TEXT(D$1-1,"mmmyy")&"'!B"&ROWS($1:2))

which can be copied into D3. Then when you have created a sheet for July, just change the date in D1 to 1/07/2015 - this also makes it more obvious which months you are comparing.

Hope this helps.

Pete