MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Output Retention Question!


Posted by Richard Larocque on February 06, 2002 6:11 PM

Alright! What I need to do is as follows: In C2:C14, let's say, there are scores. C1 is the average of these scores. The value in C1 which changes daily because the corresponding scores in C2:C14 change daily, must now appear in the B column. However, the values in the B column must always retain their values. For example, if on Jan. 1 the value in C1 is 40, the value in B2 is 40. If the value of C1 on Jan. 2 is 38, the value in B3 becomes 38 while the value in B2 remains at 40. Thanks. I realize that this may be a VBA question!


Posted by Tom Urtis on February 06, 2002 9:43 PM

Right click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_Calculate()
[B65536].End(xlUp).Offset(1) = [C1]
End Sub

Tom Urtis

Posted by Tom Urtis on February 06, 2002 9:51 PM

One more thing...

If by chance you have other calculations happening on that sheet which have nothing to do with the average calculation in C1, then use this exclusionary code instead:

Private Sub Worksheet_Calculate()
If [C1] = [B65536].End(xlUp) Then Exit Sub
[B65536].End(xlUp).Offset(1) = [C1]
End Sub

HTH

Tom Urtis

Posted by Richard Larocque on February 07, 2002 7:05 AM

Ahhhhhhhhhhh!

Thanks a bunch Tom! You came through. Now take the rest of the day off. Regards.