MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Big Excel problems in Canada!

Posted by Richard Larocque on February 06, 2002 9:32 AM

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.

Posted by Rick I thought I was an XLS GOD Hollingsworth on February 06, 2002 2:17 PM

Follow up:
I think what you are trying to do is shift the results from the previous days down. It would be easier to have the value in B3 be 40 and the value in B2 change to 38. This can be accomplished by copying b2 to the end and pasting special as value. Keystrokes are:
from "b1"
shift + cont + arrow down
cont + c
alt + e

To do what you want, change the equation for cell b1 to =$C$1
on day 1, copy b1 special as value to b2
copy b1 to b3.
on day 2 prior to refreshing the data in C, copy b3 as paste special value, refresh column C and copy b1 to b4.
This is a little kludgy, but if you must have them sorted from earliest to latest this will work.