Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    New Jersey, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating a Value in a Cell (2000)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating a Value in a Cell (2000)

    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?

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Location
    New Jersey, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating a Value in a Cell (2000)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating a Value in a Cell (2000)

    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.

  5. #5
    New Lounger
    Join Date
    Jul 2004
    Location
    New Jersey, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating a Value in a Cell (2000)

    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

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating a Value in a Cell (2000)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating a Value in a Cell (2000)

    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.

  8. #8
    New Lounger
    Join Date
    Jul 2004
    Location
    New Jersey, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating a Value in a Cell (2000)

    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?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating a Value in a Cell (2000)

    <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> 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.

  10. #10
    New Lounger
    Join Date
    Jul 2004
    Location
    New Jersey, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating a Value in a Cell (2000)

    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!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating a Value in a Cell (2000)

    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.

  12. #12
    New Lounger
    Join Date
    Jul 2004
    Location
    New Jersey, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating a Value in a Cell (2000)

    *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.

  13. #13
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Updating a Value in a Cell (2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •