Need help automatically updating and workbook to just allow use to drop the new months numbers in.

[Clearyok]

I have linked a file that is a balance sheet, Income statement, cash flow, equity, and all historical data (BS and IS). I currently add a new row into historical for the new month then update all the formulas on sheets 1-7 manually. I need a way to automatically update all the formulas for the new months numbers with a macro or dynamic ranges or something that doesn't take days. Please help.

https://docs.google.com/spreadsheet/ccc?key=0AsA2OEy5kdKUdDBDWGkzRWFwU3h3NnlnSWRUVVNkcnc&usp=sharing

 

[ChrisR]

As a relatively quick solution..........Without having to make major changes to the way your data is set up........

On the summary tab (Tab 1 for the balance sheet) using the cash as the example........

Can you not set the formula up to be =OFFSET('.8) BS Historical'!B,0,A1)

(I.e =OFFSET(X,0,A1) with the X being the reference on the historic.

That way your formula would look up December 2012 and the A1 at the end of the formula would be your period number in cell A1...........So if you are in period 10 then the formula would return the value 10 columns to the right from December 2012 (I.e. October).

This would then update automatically when cell A1 changed to the next period..........

 

[Clearyok]

.

 

[Clearyok]

Wow this is the first time I can actually understand the offset reference, LOL. Thank you so much. Now I just have to go in and re-build all of the formulas to reference appropriate cells. Then next month when I add a new month I will just change the reference of "A1" or lets say march is #17 so April would be 18 and I would just ctrl+f (find and replace) all of the 17's to 18's?

 

[ChrisR]

No...........Because you would just need to change the number in A1............So If this month A1 = 4 then next month just change A1 to 5 and all your formulas would update automatically. No need to find/replace.........It's just one figure change.........Just a bit of work setting up the formulas

 

[Clearyok]

When I do that the A1 keeps trying to refer to the Sheet I'm on not the sheet I am trying to reference. This is the formula I am using for cell C11 (=OFFSET('BS Historical'!A11,0,A1)+OFFSET('BS Historical'!A20,0,A1)+OFFSET('BS Historical'!A37,0,A1)).
On Sheet BS Historical A1 I have 18 in there and it keeps refering to the current sheet I'm on (.1BS) cell A1 and it's text so I keep getting #Value!

 

[ChrisR]

Just change A1 to the sheet reference and this would solve it wouldn't it? I.e. Highlight A1 and then click the sheet you want it to refer to and click in A1 on that sheet..........