Return the relative formula of another cell

**fatpiggy123** · 09-21-2009, 07:00 AM

Hi all,

I currently have a list of totals that i want to easily be able to change the cells that make up this total.

I have been trying to set up one cell that contains the formula and then the remaining totals below are linked to this cell. Thus I change the formula in the top cell all the other totals also change.

Is there any easy way to do this?

Thanks in advance,

Mike

**martindwilson** · 09-21-2009, 07:55 AM

got an example workbook?

**fatpiggy123** · 09-22-2009, 06:36 AM

Hi,

Basically i need to change the formula each month so i can change the foracasted figures to actual, like a rolling budget.

I would like to be able to change the formula in the grey cell (AR4) and the totals in column AR all change to meet this formula.

For example

Septemebr =H4+K4+N4+Q4+T4+W4+AA4+AD4+AG4+AJ4+AM4+AP4

October would need to be =H4+K4+N4+Q4+T4+W4+Z4+AD4+AG4+AJ4+AM4+AP4
(AA changes to Z)

The cells in Column AR would then all change once the formula is changed in AR4.

Is there any way to do this or a better way I am not aware of.

Many thanks

Mike

**martindwilson** · 09-22-2009, 06:53 AM

cant open that at work save as xls and repost

**fatpiggy123** · 09-22-2009, 11:14 AM

Is there any way to convert the doc without having to buy software to do so, Sorry i am kind of new to this.

Thanks

**martindwilson** · 09-22-2009, 11:18 AM

when you save it just save as xls not xlsm
office button/save as/choose xl97-xl2003 workbook

**fatpiggy123** · 09-22-2009, 11:55 AM

thanks....

**martindwilson** · 09-22-2009, 02:13 PM

whats with all the hidden columns?
ok i un hid them but why are you summing the first one of a month then change to the second col of a month? you go from x7 to ab 7 instead of aa7?

**ChemistB** · 09-22-2009, 03:57 PM

Sorry FP, I can't follow your example. Your data should start in row 7 not 4 and both AA6 and Z6 are labeled as October 09 (so why would 1 be included with Sept and the other with Oct?). This sounds like something one could do with SUMPRODUCT using the headers to determine if you should sum specific columns. Revise your formula and instructions based on your example spreadsheet and maybe I can help you.

**fatpiggy123** · 09-23-2009, 04:15 AM

Sorry I accidently changed the ss slightly before re-saving it. Basically i need a rolling budget so actual figures are taken (white columns) for months occured then forecasted figures (light blue) for months left in the year to give a combined Year to Date figure. Colums are hidden because I only want to see the relevant actuals or forecasted figures along with the variance in the current month. I currently have macro's (removed) for each month to hide and un-hide the relevant columns each month but could not figure out a way to easily/quickly change the formula in each Year To Date column without manullay changing the formula and dragging down.

I was trying to put a formula in the grey box which when changed to reflect the current month would change each YTD figure below aswell. Not sure if this is the most logical/best way but just trying to find something quick and easy as i have over 25 similar sheets to currenty do this in.

Thanks again,