#1
|
|||
|
|||
Conditional calculation of cell entries
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. |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
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
|
#4
|
||||
|
||||
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] |
#5
|
|||
|
|||
Well you get circular reference error doing that, hence why i was asking the question
|
#6
|
||||
|
||||
What formula are you using?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
In Cell C6 for daily amount, formula is D6/365 In Cell D6 for annual amount formula is C6*365
|
#8
|
||||
|
||||
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] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Question re: Conditional formatting (cell occurs after another cell) | Saywarder | Excel | 1 | 04-20-2015 11:29 AM |
Conditional Cell Formatting: Alternating Cell Checks | corbott | Excel | 4 | 12-16-2014 01:51 PM |
Conditional Formatting based on Value of Another Cell | SteveBump | Excel | 3 | 03-07-2014 09:16 AM |
Conditional formatting value change of same cell | tcalhoun | Excel | 0 | 04-25-2013 07:25 AM |
How can I fill cell color starting from Cell D5 using Conditional formatting instead | Learner7 | Excel | 0 | 07-08-2010 05:50 AM |