I have set up a P&L spreadsheet showing current month and year to date figures.
How do I copy this to a new worksheet and the formulas change?? ie Januarys YTD = January + Decembers YTD so I want to copy this to February so February YTD = February + January YTD without having to go in and change the formulas????
I'm not sure I follow your requirements but, to get the ball rolling, here's a layout I've seen used to good effect in the past that might give you some ideas.
A
B
C
D
E
F
1
Jan
Feb
YTD
Mar
Apr
2
CY Data
1
1
2
3
PY Data
1
2
3
3
4
The formula in D2 calculates the sum of the items from B2 to the column prior to D (in this case C). The formula in D2 is =SUM(B2:INDEX(2:2,COLUMN()-1))
At the end of March, cut and insert column D into the column E position - it will now show the Jan-Mar YTD.
There are lots of other ways to achieve the same thing with different data layouts. Perhaps you could paint a fuller picture of the layout(s) you'd like to achieve as a final presentation?
Regards,
-- Edited by Onion4Sage on Monday 16th of February 2015 11:04:37 AM
As far as I know, you can't do this in Excel directly. OpenOffice allows you to specify sheets as absolute or relative references (that is, using a $ or not), so copying a sheet which references relatively the previous sheet will stay as the previous sheet in the new one, which is what you are after.
You could do it in Excel indirectly by using sheet names in formulae. First you need to know what the current sheet is...
...and then use a table to look up the name of the previous month's sheet, given the current sheet name. Once you have that, you can use the INDIRECT function to calculate the cell you want to access in the other sheet using the sheet name as a variable, similar to what is shown in...
However, it's no big deal to copy the sheet to a new month and then use search and replace to change all the sheet names to the new, "previous" month. It's not automated, but still pretty quick to do.
(I know what I mean!!)
