keep old cell value and new cell value

[bebazza]

Hello
I have a spreadsheet that has columns a to h
column a pay no e.g. No 1, No 2 etc
column b type F,P,C etc
column c gross salary
column d per week
column e per hour
coulmn f period from
column g period to
column h day
In coulmn c i have formula =if(j7>0,$j$7*$j$8,0)

In cell I5 i have no of hours and in cell J5 Ihave 38la In cell I6 i have
per hour and in cell J6 I have $30-00
In cell I7 i have per week and in cell J7 i have formula =J6*J5
I have used the cell J7 as a relative cell in column C.

The problem I have is that when the hourly rate changes all the rows change
iwth the new hourly rate I need to keep old rows with old gross salary and
then for the new hourly rate to take over from a new date. would it be
possible to change these formulaes to handle this.

Thank you.

 

[Gord Dibben]

Only by storing the old value as that value.

Copy and paste special>values into a new column for storage.

This would have to be done prior to changing the hourly rate.


Gord Dibben MS Excel MVP

 

[Otto Moehrbach]

be
It's really hard to follow what you are saying, except for the last
paragraph, so I will address the last paragraph only. I take it that you
have the hourly rate in some cell and you use that cell's address in some
formulas. Apparently, you have these formulas in a lot of rows. You want,
when the hourly rate changes, for all existing values to stay the same and
for only the new data/formulas to pick up the new hourly rate. Is that
correct?
You can't write the formulas to do this. But there is a way to get what you
want. Basically, you would need a Worksheet_Change macro that would do
something ONLY if and when the value in that one cell, the hourly rate cell,
changes. You would need to write the macro to take all the existing data
and convert it to values only and remove all formulas from that existing
data. That will freeze those values. Any formulas that are outside of the
existing data will pick up the new hourly rate.
Assuming that the last occupied cell in Column A is in the last row of your
existing data, and cell J1 holds the hourly rate, and row 1 has headers, and
your data starts in row 2, the macro would look something like the
following. This is a sheet event macro and must be placed in the sheet
module of your sheet. To access that macro, right-click on the sheet tab,
select View Code, and paste this macro into that module. "X" out of the
module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal As Double
Dim NewVal As Double
Dim rColA As Range
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("J1")) Is Nothing Then
NewVal = Target.Value
Application.EnableEvents = False
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
rColA.Resize(, 8).Copy
Range("A2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.EnableEvents = True
End If
End Sub