Calculating YTD

[kev959]

This seems like such an easy issue but yet I have "writer's" block in reaching a solution.

Basically I have a workbook which contains a tab for each month. Each month will have either actual or projected data feed into it depending on where in the year we are. So Jan is now actual figures but Dec 2014 are projected figures. BTW, all tabs are formatted similar. and all tabs have numerous rows and columns. I want to set up a new tab that will only total the monthly data that I ask it for.

I've done this before but only retrieving data from only one tab. I did so by adding in a dropdown menu and if you selected say June only YTD data to June would be input into a table. And my data was static (an annual budget). In this case I now have multiple tabs, and many more cells that need to be summed.

Any thoughts? I'm initially thinking to do the same but obviouly on a much larger scale. But it seems like there may be easier ways than I'm realizing.

Thanks!

 

[Weaver]

Sorry if this is a dumb question, but do you need to have 1 tab per month? When I do stuff like this, I make the month a field in the 1 tab all my data goes in which tends to simplify everything.

Otherwise, you should look at using INDIRECT() to compose formula addresses which can incorporate the name of the tab, drawn from a cell with validation on it.

 

[kev959]

Unfortunately, we do need to have the months on seperate tabs. One reason is that the data we compile consists of multiple accounts (200 rows) and is broken out by business units (columns from A to EH). A bit complex of a spreadsheet which makes this perplexing.

I thought I came up with an easy solution.This was to simply sum the tabs Jan:Dec for each cell. There are other tabs in the workbook beside the month tabs. Problem then is that other users may end up reordering the tabs.