Changing tab selection with a formula

[ngfi]

Hi everyone,

I m actually doing a reporting for my company. I created an Excel Sheet where all the informations come. The first tab is like a selection menu where I choose which month I want to look at. The last one is a reporting where various informationa appear, like sales, revenues.... between these two tabs there are one tab for evrey month, and every month a new tab will be added with key figures, always using the same frame work

I have a problem; in my reporting tab, there is a section called LTM (Last tewlve month). Its aim is to add the figures of the last twelve month in order to compare it to the actual/selected month. For example, if I select March 2010 in the first tab, in the LTM section, there sould be an addition of the figures from March 2010 to April 2009.

The key point here is that the twelve month selection depends on the month choose at the beguinning, so it evoluing. And i don't know the right formula for this.

Thank a lot

Thibault

[JBeaucaire]

You can use the INDIRECT() function to construct a formula using text strings. This would enable you to, for instance, convert this hardcoded formula:

=SUM('April 2009:March 2010'!A1)


...into a formula that uses text found in cells to figure out the month strings. If selection box for the month is in B5 and it says "March 2010", then this formula gives you the date string from a year earlier:

=TEXT(EOMONTH(B5,-12)+1, "MMMM YYYY")

Result: April 2009

Now, we insert that into an INDIRECT() function to piece it all together:

=INDIRECT("'" & TEXT(EOMONTH(B5,-12)+1, "MMMM YYYY") & ":" & TEXT(B5, "MMMM YYYY") & "'!A1")

There are so many different things about your data/sheetnames/formats that can complicate this, we'd have to actually see your workbook to spot them all. But you may be able to get it working from this. If not, post your workbook.

[ngfi]

Hi,

Thank you for this answer. I m gonna check that.
If you want to look at my workbook, here it is. The two columns I want to fill in are I & J in the reporting tag. don't worry about the others, I know how to make them work.
In the source tab, you can select the month and the year in I6 & J6. I created only 5 month tab for the example.

Thanks

http://dl.dropbox.com/u/48225536/Reporting.xlsx

Thibault

[ngfi]

And please pay no attention to the formulas in the cells in I and J, they are wrong and here just by mistake.

Thank for your help

[JBeaucaire]

So, what did you want me to look at?