Selfreferencing ?

[rvExcelNewTip]

I face an easy question and possibly a complicated solution: A data variable is captured daily and entered into a spreadsheet cell ($A$1). I want cell $B$1 to list the highest value ever entered into cell $A$1. The value in cell $A$1 does not need to be preserved (it is overwritten the next day by the new measurement)

If cell $B$1 could contain the following formule, the solution would be easy:
=if($A$1>$B$1,$A$1,$B$1)
but Excel doesn't allow selfreferencing., so that was a nice dream.

Some suggestions?

[OZDOC1050]

Some suggestions?

Yes you could use a macro similar to below and can be linked to and event if
needed to automate it

Sub A1BIGGER()
If Range("A1").Value > Range("B1").Value Then
Range("B1").Value = Range("A1").Value
End If
End Sub

pasted into sheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value > Range("B1").Value Then
Range("B1").Value = Range("A1").Value
End If
End Sub

pete
--
(][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][)


"rvExcelNewTip" <[email protected]>
wrote in message
news:[email protected]...
>
> I face an easy question and possibly a complicated solution: A data
> variable is captured daily and entered into a spreadsheet cell ($A$1).
> I want cell $B$1 to list the highest value every entered into cell $A$1.
> The value in cell $A$1 does not need to be preserved (it is overwritten
> the next day by the new measurement)
>
> If cell $B$1 could contain the following formule, the solution would be
> easy:
> -=if($A$1>$B$1,$A$1,$B$1)-
> but Excel doesn't allow selfreferencing., so that was a nice dream.
>
> Some suggestions?
>
>
> --
> rvExcelNewTip
> ------------------------------------------------------------------------
> rvExcelNewTip's Profile:
> http://www.excelforum.com/member.php...o&userid=15668
> View this thread: http://www.excelforum.com/showthread...hreadid=498623
>

[Niek Otten]

Hi pete,

http://www.mcgimpsey.com/excel/accumulator.html

--
Kind regards,

Niek Otten

"OZDOC1050" <[email protected]> wrote in message
news:%[email protected]...
> Some suggestions?
>
> Yes you could use a macro similar to below and can be linked to and event
> if needed to automate it
>
> Sub A1BIGGER()
> If Range("A1").Value > Range("B1").Value Then
> Range("B1").Value = Range("A1").Value
> End If
> End Sub
>
> pasted into sheet
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Range("A1").Value > Range("B1").Value Then
> Range("B1").Value = Range("A1").Value
> End If
> End Sub
>
> pete
> --
> (][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][)
>
>
> "rvExcelNewTip"
> <[email protected]> wrote in
> message news:[email protected]...
>>
>> I face an easy question and possibly a complicated solution: A data
>> variable is captured daily and entered into a spreadsheet cell ($A$1).
>> I want cell $B$1 to list the highest value every entered into cell $A$1.
>> The value in cell $A$1 does not need to be preserved (it is overwritten
>> the next day by the new measurement)
>>
>> If cell $B$1 could contain the following formule, the solution would be
>> easy:
>> -=if($A$1>$B$1,$A$1,$B$1)-
>> but Excel doesn't allow selfreferencing., so that was a nice dream.
>>
>> Some suggestions?
>>
>>
>> --
>> rvExcelNewTip
>> ------------------------------------------------------------------------
>> rvExcelNewTip's Profile:
>> http://www.excelforum.com/member.php...o&userid=15668
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=498623
>>
>
>

[rvExcelNewTip]

I put the original formula into the $B$1 cell and checked the Iterations option on the Calculations Tab sheet. Works Fine! I left the number of iterations unchanged as there happens to be a GoalSeek function on that particular worksheet. No noticeable performance degradation (if any).

Thanks!