Updating a Value in a Cell (2000)

Topic

New Reply

Can someone help me please. I have a spreadsheet that I want to update with ongoing price increases. When I open the worksheet I want to be able to make a change to, let’s say B2 and have that change added to the formula that resides in F2(the formula in F2 = $D4 *E4), is there anyway to do this without inserting another column? I apperciate any help anyone is able to provide.

Reply | Quote

Replies

I don’t quite understand what you want. Cell F2 already contains a formula =$D4*E4. What exactly should happen if the value of B2 changes? Wouldn’t a formula =$D4*E4+B2 do what you want?

Reply | Quote

First I want to thank you for responding Hans. Thank you so much.. This is what I’m trying to do.
Cell F2(I really should say Column F), currently has the formula D * E. which works fine for now, but when there’s a pricing increase or decrease in column B, I want to be able to add the Increase which is contain in column B to be added to the formula in Column F but only if colmun b changes. I guess what i’m saying is, Column F maintains the current formula & only updates the value when column B changes. Does that make sense?

Reply | Quote

I’m sorry, I must be dense tonight, for I still don’t understand. The outcome of a formula in Excel only changes if one of the parts changes. So if F2 contains =D2*E2+B2 (or something similar), the value of F2 will only change if B2, D2 or E2 changes. If F2 contains =D2*E2, the value of F2 is the product of D2 and E2, there is no way to add anything to it without changing the formula.

Reply | Quote

You’re not being dense this evening :-), I think it might be how I’m explaining it. I’ve attached a copy of the worksheet as it exists today. What I’m trying to incorporate going forward is, if there’s a change to the value in column B then add that change to the formula in F, but only if there’s a change/update.

Thanks

Reply | Quote

I still don’t understand. Please give an example of what you want the worksheet to do. Change the value in B4 and tell us what you wnat to happen in F4.

Reply | Quote

I still don’t understand. Please give an example of what you want the worksheet to do. Change the value in B4 and tell us what you wnat to happen in F4.

Reply | Quote

Let’s take row 4 as example. In the workbook you posted, we have:
B4 = 1.20
D4 = 4
E4 = 9.68
F4 contains the formula =D4*E4, so its value is 4*9.68 = 38.72.
Now, the user changes the value of B4 from 1.20 to 1.30. What do you want to happen exactly? Please be as specific as you can.

Reply | Quote

I want the 1.30 to be added to the value of 38.72, making the value is F4 to change from 38.72 to 40.02, but I don’t want the formula that will be calculating this change to be housed in F4. Is that possible?

Reply | Quote

That is ugly! This cannot be done without changing the formula, for the formula says ‘multiply D4 and E4’, no more and no less. The following event procedure will adjust the formula, but it will become very unwieldy over time, and there is no undo if the user makes a mistake!

Right click the sheet tab.
Select View Code.
Copy the following code into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Application.EnableEvents = False
If Not Intersect(Target, Range(“B4:B18”)) Is Nothing Then
For Each oCell In Intersect(Target, Range(“B4:B18”))
oCell.Copy
oCell.Offset(0, 4).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationAdd
Next oCell
End If
Application.EnableEvents = True
Application.CutCopyMode = False
Set oCell = Nothing
End Sub

Switch back to Excel.
Save the workbook.
Change a cell in B4:B18 to test the effect – look at the corresponding cell in column F.

I don’t like this method at all, but the alternative is to use a separate column to accumulate the changes, and you didn’t want that either.

Reply | Quote

You are so correct. It works but it’s not pretty, especially not being able to undo. If i’m correct, what’s happening is that value in column B is when changed is added onto what exists in Column F. I really apperciate your help Hans, you’re the best!

Reply | Quote

It would be much easier if the user entered the cumulative value in B4 etc. So if it is 6.80 now, and there is an increase of 1.30, the user would enter 8.10 (being the result of 6.80+1.30) in B4. The formula in F4 would be simply =D4*E4+B4, and no code would be necessary.

Reply | Quote

*sigh* so true. Not to add fuel to the already flaming fire but say for instance the information contact in columns E & F (representing bdl & sq) where across several columns but representing different regions. The formula wouldn’t work across the broad because the price increase doesn’t happen across the regions, it’s selective. What a headache. it looks like this workbook will have to be maintained in a data entry fashion or they need to automated this updates asap.

Reply | Quote

Why not make column B contain zeros, and simply make F = D*E+B as suggested before?

Whenever you want to make a change you alter the value in B for the relevant rows.

Reply | Quote

Why not make column B contain zeros, and simply make F = D*E+B as suggested before?

Whenever you want to make a change you alter the value in B for the relevant rows.

Reply | Quote

*sigh* so true. Not to add fuel to the already flaming fire but say for instance the information contact in columns E & F (representing bdl & sq) where across several columns but representing different regions. The formula wouldn’t work across the broad because the price increase doesn’t happen across the regions, it’s selective. What a headache. it looks like this workbook will have to be maintained in a data entry fashion or they need to automated this updates asap.

Reply | Quote

It would be much easier if the user entered the cumulative value in B4 etc. So if it is 6.80 now, and there is an increase of 1.30, the user would enter 8.10 (being the result of 6.80+1.30) in B4. The formula in F4 would be simply =D4*E4+B4, and no code would be necessary.

Reply | Quote

You are so correct. It works but it’s not pretty, especially not being able to undo. If i’m correct, what’s happening is that value in column B is when changed is added onto what exists in Column F. I really apperciate your help Hans, you’re the best!

Reply | Quote

That is ugly! This cannot be done without changing the formula, for the formula says ‘multiply D4 and E4’, no more and no less. The following event procedure will adjust the formula, but it will become very unwieldy over time, and there is no undo if the user makes a mistake!

Right click the sheet tab.
Select View Code.
Copy the following code into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Application.EnableEvents = False
If Not Intersect(Target, Range(“B4:B18”)) Is Nothing Then
For Each oCell In Intersect(Target, Range(“B4:B18”))
oCell.Copy
oCell.Offset(0, 4).PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationAdd
Next oCell
End If
Application.EnableEvents = True
Application.CutCopyMode = False
Set oCell = Nothing
End Sub

Switch back to Excel.
Save the workbook.
Change a cell in B4:B18 to test the effect – look at the corresponding cell in column F.

I don’t like this method at all, but the alternative is to use a separate column to accumulate the changes, and you didn’t want that either.

Reply | Quote

I want the 1.30 to be added to the value of 38.72, making the value is F4 to change from 38.72 to 40.02, but I don’t want the formula that will be calculating this change to be housed in F4. Is that possible?

Reply | Quote

Let’s take row 4 as example. In the workbook you posted, we have:
B4 = 1.20
D4 = 4
E4 = 9.68
F4 contains the formula =D4*E4, so its value is 4*9.68 = 38.72.
Now, the user changes the value of B4 from 1.20 to 1.30. What do you want to happen exactly? Please be as specific as you can.

Reply | Quote

You’re not being dense this evening :-), I think it might be how I’m explaining it. I’ve attached a copy of the worksheet as it exists today. What I’m trying to incorporate going forward is, if there’s a change to the value in column B then add that change to the formula in F, but only if there’s a change/update.

Thanks

Reply | Quote

I’m sorry, I must be dense tonight, for I still don’t understand. The outcome of a formula in Excel only changes if one of the parts changes. So if F2 contains =D2*E2+B2 (or something similar), the value of F2 will only change if B2, D2 or E2 changes. If F2 contains =D2*E2, the value of F2 is the product of D2 and E2, there is no way to add anything to it without changing the formula.

Reply | Quote

First I want to thank you for responding Hans. Thank you so much.. This is what I’m trying to do.
Cell F2(I really should say Column F), currently has the formula D * E. which works fine for now, but when there’s a pricing increase or decrease in column B, I want to be able to add the Increase which is contain in column B to be added to the formula in Column F but only if colmun b changes. I guess what i’m saying is, Column F maintains the current formula & only updates the value when column B changes. Does that make sense?

Reply | Quote

I don’t quite understand what you want. Cell F2 already contains a formula =$D4*E4. What exactly should happen if the value of B2 changes? Wouldn’t a formula =$D4*E4+B2 do what you want?

Reply | Quote