Liz,
Copy the code below, right click on the worksheet tab, select "View code"
and paste the code into the window that appears.
HTH,
Bernie
MS Excel MVP
Private Sub Worksheet_Calculate()
Dim myCell As Range
Dim myVal As Double
If Range("E1").Value < Range("F1").Value Then
Application.EnableEvents = False
myVal = Application.WorksheetFunction.Max(Range("A1:D1"))
For Each myCell In Range("A1:D1")
If myCell.Value = myVal Then
myCell.Value = myCell.Value + Range("F1").Value - Range("E1").Value
Application.EnableEvents = True
Exit Sub
End If
Next myCell
End If
Application.EnableEvents = True
End Sub
"Liz C" wrote in message
...
Sorry, I did mis-type. Yes, I guess what I need to know is how to make it
go
to the cell that has the max value.
a1=50 b1=25 c1=10 d1=3 e1=(calculated)88 f1=90
I want it to go add 2 to a1 (because it is the max of a1 thru d1). I
figured I would need to do it with a macro in order to avoid a circular
reference, but don't know how to tell it to go to the cell with the max
value.
Thanks.
Liz
"Bernie Deitrick" wrote:
Liz,
Now it's J1, not D1? or did you mis-type?
And do you actually want to change the value of the cell with the max
value?
Then you would need a macro.
But the formula will simply give you the same result, but in another
cell.
HTH,
Bernie
MS Excel MVP
"Liz C" wrote in message
...
I want the difference between F1 & E1 to be added to the largest of A1
thru
J1. How can I have it figure out which cell is the largest and then
go to
that cell and increase it's value by the difference between F1 & E1?
Thanks, Bernie.
"Bernie Deitrick" wrote:
Liz,
Depends on which difference you want. Try
=E1-F1+MAX(A1:D1)
or
=F1-E1+MAX(A1:D1)
or even
=ABS(E1-F1)+MAX(A1:D1)
HTH,
Bernie
MS Excel MVP
"Liz C" wrote in message
...
Hi.
I have a range of cells from A1 thru D1 with a total of A1:D1 in
E1
and a
total to compare that to in F1.
I want to take the difference between E1 and F1 and add it to the
cell
between A1 and D1 that has the largest value.
Any ideas how I might accomplish this?
Thanks!
|