Create non-circular Reference Formula...or something...

[jburchett]

I have a spreadsheet with one column (C) that has the yearly expenditure. Column (B) has the monthly expenditure.


I want to be able to change Column B and it automatically update Column C.


i.e. Column C (yearly) is $1000. If I change column B (monthly) to $150, it should automatically change C to $1150. Next month, if I change column B to $100, column C should automatically change to $1250.


There has to be a way to do this. Yes, I read your blog about circular references and changing it to enable iterative calculations, but that didn't seen to help.


BTW, LOVE your website and your teaching style! You make me laugh!

 

[keymaster]

Hi Jburchett, Welcome to Chandoo.org.


Is there is a logic behind how much yearly value changes for change in monthly value.

For example, if monthly expense is $x, what would yearly be? - 12x?


if so, in column C, just write =12*B1 and this should do.

 

[YD]

good afternoon

if there is no relation between m income and a income

anual income on C2 monthly income on B2

just recard a macro

click on record macro in D2 =C2+B2 and copy D2 and paste it on C2, stop the recording you will get the updated figure add a button and assign the macro to it

i am a newbie trying to learn cheers

if you get errors i can send ayou the format contact me on darneyogesh@yahoo.in

 

[YD]

http://speedy.sh/QAJh5/non-circular-Reference-Formula.xls


please check i have created one format

Enable macro after opening the file

Enjoy

YD