Hi all - this is my first post so hope I don't break any protocols (and apologies if I already have!).
I have an Excel 2003 question. I have a x5 tab spreadsheet showing financial info:
Budget - monthly budget data for each item (B)
Actuals - monthly actuals data for each item (A)
Prior Year - monthly prior year actual data for each item (P)
Year to Date - current situation at any point in the year based on a dropdown list of months (Y)
DropDown lists - list of the months for tab Y
The first x3 tabs (B, A, P) have exactly the same rows/columns.
Tab Y has the same row entries as tabs B, A, & P but has different column headings. It is a summary page of the financial position at the end of any given month through the financial year.
My aim is that by selecting a month from the dropdown list of months in cell F2, the 3 columns indicated in that tab (columns D, F and N) will access the correct data from their respective other tabs and consolidate it into the right amount in the right cell.
Please take a look at the sample file and note the comments I've included about what I need the spreadsheet to put into the cells dependent on the month selected from the drop down list.
I don't think I can do this using a Pivot table. I can't figure out if I can do it with SUMPRODUCT or SUBTOTAL or COUNTIF, or some combination thereof.
So, I'm stumped. Might not be possible at all - or might only be possible through some re-arrangement of the source data (which I would be prepared to do if necessary). Added complication is the people I need to distribute this to may be using later versions of Excel (not sure if that is an issue anyway?).
All help gratefully received - even if it is to get to a "no, not possible in Excel 2003" result which will stop me wasting cycles trying to get it done.
Many thanks in advance
Olly [PS I'm no programmer, macro rather than VB is potentially my limit at the mo
]
I have an Excel 2003 question. I have a x5 tab spreadsheet showing financial info:
Budget - monthly budget data for each item (B)
Actuals - monthly actuals data for each item (A)
Prior Year - monthly prior year actual data for each item (P)
Year to Date - current situation at any point in the year based on a dropdown list of months (Y)
DropDown lists - list of the months for tab Y
The first x3 tabs (B, A, P) have exactly the same rows/columns.
Tab Y has the same row entries as tabs B, A, & P but has different column headings. It is a summary page of the financial position at the end of any given month through the financial year.
My aim is that by selecting a month from the dropdown list of months in cell F2, the 3 columns indicated in that tab (columns D, F and N) will access the correct data from their respective other tabs and consolidate it into the right amount in the right cell.
Please take a look at the sample file and note the comments I've included about what I need the spreadsheet to put into the cells dependent on the month selected from the drop down list.
I don't think I can do this using a Pivot table. I can't figure out if I can do it with SUMPRODUCT or SUBTOTAL or COUNTIF, or some combination thereof.
So, I'm stumped. Might not be possible at all - or might only be possible through some re-arrangement of the source data (which I would be prepared to do if necessary). Added complication is the people I need to distribute this to may be using later versions of Excel (not sure if that is an issue anyway?).
All help gratefully received - even if it is to get to a "no, not possible in Excel 2003" result which will stop me wasting cycles trying to get it done.
Many thanks in advance
Olly [PS I'm no programmer, macro rather than VB is potentially my limit at the mo
]