Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
keep old cell value and new cell value
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(j70,$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. -- be |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
keep old cell value and new cell value
Only by storing the old value as that value.
Copy and paste specialvalues into a new column for storage. This would have to be done prior to changing the hourly rate. Gord Dibben MS Excel MVP On Fri, 11 Jul 2008 23:24:00 -0700, bebazza wrote: 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(j70,$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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
keep old cell value and new cell value
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 "bebazza" wrote in message ... 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(j70,$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. -- be |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I make a blank cell in a formula cell with a range of cell | Excel Discussion (Misc queries) | |||
Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
cell data not validated if navigating cell to cell with mouse | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |