Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Melbourne, Australia
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Using cell formula to alter another cell

    I have an application where several independant complex calculations are used to produce a result. I would be able to simplify the work and understanderbility of the spreadsheet if I could alter the value of a different cell from the calculating cell. An simple example would be:
    in cell C1, =IF(A1=B1,D1=D1+1,D1=D1-1)
    Of course, the true and false paths of the IF statement are treated as logical expressions and resolve to TRUE or FALSE.

    Is there any way to make a change to another cell from within a cell?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Warwickw,

    You will need VBA to do that. Something like:

    [d1] = IIf([a1] = [b1], [d1] + 1, [d1] - 1)

    Can you specify exactly what you want to achieve?

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Warwickw,

    I don't know any way to do exactly what you want. That said you can easily accomplish what you are after by using a small Event Macro that will fire off any time A1 or B1 is changed and then perform the desired calculation and place the value in D1 as follows:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
       Dim isect As Range
       
       Set isect = Application.Intersect(Range("A1:B1"), Target)
       If isect Is Nothing Then  'Ranges do not overlap!
       Else
         '***Prevent following code from refiring Change Event ***
         Application.EnableEvents = False
         [D1].Value = [D1].Value + IIf([A1] = [B1], 1, -1)
         Application.EnableEvents = True '*** Reset Events ***
       End If
    
    End Sub
    Note: you'll have to save the workbook as .xlsm or .xlsb as well as placing in in a trusted location (Google Excel Security settings) so the macro will work.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Melbourne, Australia
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks, Maudibe and RetiredGeek,
    I suspected I would need to do something like that, was just hoping that there might be a way of achieving the change without resort to VBA.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Warwickw,

    The closest you can come to what you are looking for without using VBA would be to evaluate the comparison between cells A1 and B1 then based on the value of another cell, say C1, cell D1 will display the value incremented or decremented by 1.

    Instead of using cell C1 as the base value you can use any cell on the sheet. if you do not want the base value in view, then you can place it on the same sheet hidden by the font color being white, in a remote column, on another sheet, on a hidden sheet, behind an image, etc.,
    Cell based on cell.png
    Last edited by Maudibe; 2014-11-02 at 02:09.

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Melbourne, Australia
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Warwickw,

    The closest you can come to what you are looking for without using VBA would be to evaluate the comparison between cells A1 and B1 then based on the value of another cell, say C1, cell D1 will display the value incremented or decremented by 1.

    Instead of using cell C1 as the base value you can use any cell on the sheet. if you do not want the base value in view, then you can place it on the same sheet hidden by the font color being white, in a remote column, on another sheet, on a hidden sheet, behind an image, etc.,
    Cell based on cell.png
    No, what I required is to have a numeric value in the target cell, not a formula, so that I could alter its value from several places under different conditions.

    No matter, I can achieve what I need by using a more complicated formula than I desired. It is just a matter of getting my head round the complexity and using several intermediate cells to spread the complexity.

    Thanks for your help.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Post back if you find a solution.

    Good luck,
    Maud

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    Warwickw (2016-01-14)

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Maud

    ..maybe he needs to use Excel's scenario manager???


    zeddy

Posting Permissions

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