Conditional calculations of cell entries

I am trying to set up a spreadsheet which will automatically work out the figure on entering the data in the appropriate cell and use this to calculate the other appropriate figure. e.g. when given a daily figure and enter this into the daliy cell location it will automatically work out the annual figure. There is no formula in the cell where it has been entered. I have attached a typical setup of the file i would be looking to create.



Just to give a bit mor background, i am looking to create a spreadsheet which will compare domestic energy supplies on a level playing field as the suppliers are changing the ways in which they quote prices, e.g. including tax excluding tax, daily standing charge, annual standing charge etc.

I have attached a file to show this

Any help appreciated on this.

Regards

Colin

Attached Files

calc.xlsx (11.1 KB, 7 views)

Without knowing what the calculation is supposed to return, the code would be anyone's guess. As it is, it looks like the value in pounds in column D should be the amount in column c * 3.65. You don't really need a macro for that!

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

well i don't want to have to keep entering the formula in each cell, i envisaged something along the lines of using the cells in a column which would detect if an entry was made in a column it would automatically generate the adjacent column values as the data differs from different sources depending where it is from

Since it's basically the same formula for all cells, you could just input it once and copy down as far as needed...

You could even add an IF test to hide the 0s that would otherwise appear until you actually input something into the adjacent cell.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Well you get circular reference error doing that, hence why i was asking the question

What formula are you using?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

In Cell C6 for daily amount, formula is D6/365 In Cell D6 for annual amount formula is C6*365

OK, so you're trying to calculate both columns. And, since each references the other, you're getting the circular references.

Might I suggest that a better approach could be to have separate input & output columns? That way you'll have a lasting record of which figures you actually input for each provider. If you simply use a macro to update whichever column you don't input data into, you'll lose that visual record.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]