excel; replace max value with calculated value

[billheath]

This should be simple, but I am puzzled.

I have a spreadsheet that keeps track of supply costs per month. When a new max is reached, I have a call that indicates, "new max". I won't to automatically replace the number in the corresponding cell to show the new Max Value;

I tried the formula, =IF(K68="New Max", G68=B68). It returns "False each time. We had this working at one time on an earlier spreadsheet which I have been unable to find.

Any ideas will be appreciated.

 

[combo]

Seems that it is not a VBA solution, there is forum68 dedicated to other than acces MS office applications.

If you plan to return either G68 or B68 depending on K68 contents, then [tt]=IF(K68="New Max",G68,B68)[/tt].

combo

 

[mintjulep]

Formulas cannot change the value of other cells.

VBA can.

A new (possibly only preview for "insiders") feature called lambda functions might be able to.

 

[SkipVought]

Bill,

1) Why would you have such an important aggregation way down in row 68!? Just like Bob Uker, who thought he was in the "front row," it seems to me that important aggregations ought to be right at the Top, easy to find in Row 1!

2) You "have a call that indicates, 'new max'". What does that mean?

It seems that you view the "spreadsheet that keeps track of supply costs per month" and make a judgement that some aggregation has exceeded the current value and then do something like typing the new max value in a designated cell. Maybe you want a button that transfers that value to the designated cell, yes?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein