Is it possible to prevent updation of formula based cell ?

[sktneer]

Hello ExcelForum experts,

I asked the same question before but didn't get any reply. I have lot of respect for the excel gurus here in the forum and had very good experience in the past. But this time I am really struggling to get an answer to my query. Anyways I am writing it again in the hope that this time I will find the answer.

Actually in my worksheet I calculate the values in a column based on some other cell values. The problem is these referenced cell values may be updated in future dates and if it happens, The values of whole column changed because formula in the column is referencing these cells. I have a date column also.
So I want that if the values of the referenced cells are updated say on 01/09/2013 (dd/mm/yyyy), the values in the column before 01/09/2013 should remain unchanged. Is there any way to achieve this?

I am also attaching an example sheet in which I have made it clear. Plz help me. I need it to be done.

Thanks in advance.

[:) Sixthsense :)]

Just convert the formula cells as values and try the below code...

Do right click on the sheet tab and select view code and paste the below code. This code will update the Sum of data in Column-B whenever any change happens in F2 cell based on the date check.

Please Login or Register  to view this content.

[sktneer]

Thanks for your reply.

I followed what you said but its not working. Probably I didn't explain well about the requirement.

Actually the updation date in Cell A2 is changed when there is any change in values in Cells C2,D2,E2 and F2. If A2 is changed to 31/08/2013 and the values in C2,D2,E2 and F2 are changed on the same date, The values in column B should change those belong to 31/08/2013 and later until updation date and the values in CDEF(2) are not changed again in the future. All the values in column B before 31/08/2013 should remain unchanged.

I am attaching the sheet again after applying the code you suggested. In the last sample sheet I forgot to mention the updation date in cell A2 which is a key factor.

Thanks for finding some time to help me.

Waiting for your reply.

Thanks

[kvsrinivasamurthy]

Pl see attached file.

[sktneer]

Pl see attached file.

Thanks kvsrinivasamurthy ! It worked perfectly.

But I have one more query. What if the formula in column B is different than just summing the values in C2,D2,E2 and F2 ?

What changes I am supposed to make in the code then?

Please find the attachment.

Thanks for your time and support. Waiting for your reply.

[kvsrinivasamurthy]

In the below code The function is Sum(Range("C2:F2")). This is to be changed.

Please Login or Register  to view this content.

To make easy I have put the formula in J2 and code is changed so that it puts the value of J2.See the file.

[:) Sixthsense :)]

Actually the updation date in Cell A2 is changed when there is any change in values in Cells C2,D2,E2 and F2. If A2 is changed

Have you read this???

Just convert the formula cells as values and try the below code...

[sktneer]

In the below code The function is Sum(Range("C2:F2")). This is to be changed.

Please Login or Register  to view this content.

To make easy I have put the formula in J2 and code is changed so that it puts the value of J2.See the file.

Perfect. Thanks indeed. * Added to your reputation. Thanks once again.

[kvsrinivasamurthy]

Welcome for the compliments.