Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using formula or similar to paste maximum value in cell in excel 2
Hi I want to use a formula or similar to update a cell when another cell has
a higher value, and maintain this relationship, e.g. cell B1 will always contain the maximum value held by cell A1 over time where A1 is a calculated value. So I want to paste the value from A1 into B1 when A1 exceeds B1 but not otherwise and as there is a whole column I would like this to be automatic. Sorry but I can't for the life of me work out how to do it and haven't found a forum topic that does what I want. Any help greatly appreciated. Regards -- Torwalker General development dogsbody |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
using formula or similar to paste maximum value in cell in exc
I'd use the following code attached to the worksheet:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$1" Then Exit Sub End If If Target.Value Range("B1").Value Then Range("B1") = Target End If End Sub To put the code in the proper place, right-click on the worksheet's name tab and choose [View Code] from the popup list. Copy and paste the code above into the code module presented to you. Close the VB Editor and save the workbook. "Niek Otten" wrote: Read this: http://www.mcgimpsey.com/excel/accumulator.html This is mostly about SUM(), but the principles remain the same for a maximum -- Kind regards, Niek Otten Microsoft MVP - Excel "torwalker" wrote in message ... Hi I want to use a formula or similar to update a cell when another cell has a higher value, and maintain this relationship, e.g. cell B1 will always contain the maximum value held by cell A1 over time where A1 is a calculated value. So I want to paste the value from A1 into B1 when A1 exceeds B1 but not otherwise and as there is a whole column I would like this to be automatic. Sorry but I can't for the life of me work out how to do it and haven't found a forum topic that does what I want. Any help greatly appreciated. Regards -- Torwalker General development dogsbody |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
using formula or similar to paste maximum value in cell in excel 2
Hi,
This assumes column A changes because of a calculation on the same sheet, you could use the worksheet change event. Right click your sheet tab, view code and paste the code below in Private Sub Worksheet_Calculate() lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If c.Value c.Offset(, 1).Value Then c.Offset(, 1).Value = c.Value End If Next End Sub or Private Sub Worksheet_Change(ByVal Target As Range) Mike "torwalker" wrote: Hi I want to use a formula or similar to update a cell when another cell has a higher value, and maintain this relationship, e.g. cell B1 will always contain the maximum value held by cell A1 over time where A1 is a calculated value. So I want to paste the value from A1 into B1 when A1 exceeds B1 but not otherwise and as there is a whole column I would like this to be automatic. Sorry but I can't for the life of me work out how to do it and haven't found a forum topic that does what I want. Any help greatly appreciated. Regards -- Torwalker General development dogsbody |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
using formula or similar to paste maximum value in cell in excel 2
Just wanted to say thanks for replies. I should have realised a bit of code
was required. Working nicely now! Thanks again all. -- Torwalker General development dogsbody |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
using formula or similar to paste maximum value in cell in excel 2
You can use a circular reference to do this. On the Tools menu, choose
Options, then the Calculation tab. There, check the "Iterations" box. Then, use the following formula =MAX(A1,B1) B1 will be a perisistent maximum of A1. If A1 exceeds B1, B1 will get this new value. If A1 is less than B1, B1 remains the same. In other words, B1 will be the maximum value ever reached by A1, regardless of the current value of A1. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Mon, 27 Apr 2009 05:14:01 -0700, torwalker wrote: Hi I want to use a formula or similar to update a cell when another cell has a higher value, and maintain this relationship, e.g. cell B1 will always contain the maximum value held by cell A1 over time where A1 is a calculated value. So I want to paste the value from A1 into B1 when A1 exceeds B1 but not otherwise and as there is a whole column I would like this to be automatic. Sorry but I can't for the life of me work out how to do it and haven't found a forum topic that does what I want. Any help greatly appreciated. Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Similar to paste special from another workbook | Excel Worksheet Functions | |||
Find cell which returned maximum value in formula | New Users to Excel | |||
Can an Excel formula be created to paste a picture in a cell? | Excel Worksheet Functions | |||
Excel should have a simpler subtraction formula similar to "sum". | Excel Worksheet Functions | |||
Excel formula similar to a loop in Basic? | Excel Worksheet Functions |