If function to replace or keep existing value

Question

How can I use an if function to check a value in a cell and if true replace with a value from another cell, and if false keep the existing value in the cell?

For example, if the value in A5 is a city, B5 = 100,  and H5 = 50, and the value in M5 is a data validation list of city names, I want the logic for the value in cell B5 to be:

IF the city name in A5 equals the city name in M5, then put the value from H5 in B5, otherwise leave the existing value in B5 (i.e 100)

so, if A5= Camas and M5=Camas then B5 should be 50

if A5 = Camas and M5=Belgrade then B5 should remain 100.

for the equation in B5 I would want something like IF(A5=M5, H5, ???) but I don't know what to put where the ?'s are to complete the formula to keep the exiting value in A5.

Thanks!

Answer 1

It's not simple - in fact that is the whole point of Excel - the worksheet updates to the latest values used.

You should consider using event code (a macro) to store the values generated by your model anytime the value of a specific cell is selected, meaning it is about to be changed, or code that senses the change, undoes the change, re-calcs, stores the values, then redoes the change.

Lots of ways to address the issue

Answer 2

I agree that this would be an ideal solution, however I've dramatically simplified the function of my model to try to make my question clear.

In my actual model the value of H5 will be changed each time a new store is chosen in the list.  So it seems like no matter how I capture that cell value, as soon as a new store is selected and a new target value entered the reference cell will change value as well meaning the previous value is replaced.  So I need to capture the current value entered and maintain it even when a new value is subsequently entered in the same cell.

The function of the model is to facilitate our President setting goals individually for 62 different stores in 8 different metrics on a monthly basis based on a forecasting model I've built.  As he assigns a goal for each metric for each store in the model it captures the assigned value and moves it to a workbook I use to import the entire beast into our POS/KPI system.

I do have it working now as a circular reference, however it is far from ideal for multiple reasons.  And I need to keep the input simple enough so I don't have to teach him how to use Excel to use it and so the whole assignment model visually fits on his tablet screen. ;)

I just can't believe there is no way to prevent a cell value from updating based on a logical comparison!  but perhaps there really isn't!  :(  It certainly seems like it would be simple enough...

Answer 3

What you are describing requires a circular reference, which is a bad design choice. You should use another cell, with B5 hidden if needed, with the formula :

=IF(A5=M5, H5, B5)

B5 will never change, but the cell with the formula will update correctly for further use of the value.