Circular Reference in Excel IF Function

[Annelies]

Hi,

I am trying to create what I understand is a 'no-no' in Excel - a circular reference within an IF Function.

Basically, my IF Function looks like

IF(condition,<set value of cell to this>,<leave the cell unchanged>)

Does anyone know how to do this? Excel won't let me do a circular reference in this instance.

I know I can do it programmatically if need be, but I would rather not.

Thanks!!

Annelies

 

[KenWright]

Give us the condition and the ranges. It all depends what you have in your cell, as if for example you had in cell A1 the value 25, and you wanted to set up teh condition that if cell B1 was greater than 10 then A1 becomes 40, else leave it as it is, then in cell A1 you would simply put

=IF(B1>10,40,25)

Note though, you have to either hardwire in your value of 25, or if it was created via a formula then you need to replace it with the formula that created it in the first place, eg perhaps

=IF(B1>10,40,SUM(D1100))

Regards
Ken................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

You can't have a formula AND a value in a cell at the same time. One or the other.

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[byundt]

You can also accomplish your objective with either of:
1) VBA code in a Worksheet_Change sub to trap your changed value and put the results in the cell
2) Turn on iterations using the Tools...Options...Calculation menu item. Set Iterations to 1.