+ Reply to Thread
Results 1 to 11 of 11

Dynamic Subtraction

  1. 02-03-2013, 08:28 AM #1
    JKK123
    JKK123 is offline
    Registered User
    Join Date
    02-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2008 Mac
    Posts
    21

    Dynamic Subtraction

    Say I've got cells A1-A10 populated with numbers. My initial formula in cell B1 is =A1-A6. However, I'd like to strike-out cell A3 (keeping the contents visible underneath the strike-out if possible), and I'd like the formula in B1 to recognise that change, and then automatically adjust itself to =A1-A7 (the idea being that I'd like A1 subtracted by the number in the cell 5 "non-struck out" cells below it). And then if I strike out cell A5 I'd like the formula to adjust itself to =A1-A8 and so on. Does anyone know how to do this? Thanks in advance!
    Reply With QuoteRegister To Reply
  2. 02-03-2013, 10:25 AM #2
    olasa
    olasa is offline
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: Dynamic Subtraction

    Try something like this.
    //Ola


    A1:A10 Numbers striked through
    B1:B10 =IsStrikeThrough(A1) fill down
    C1: =SUMIF(B1:OFFSET(B1,,,6+COUNTIF(B1:B10,TRUE())),FALSE(),A1:OFFSET(A1,,,6+COUNTIF(B1:B10;TRUE())))


    Please Login or Register  to view this content.
    Alt+F11, Insert Module, copy and paste the above code
    Mark the problem as Solved under the Yellow bar up Top right, under Thread Tools, when you received a solution.
    It saves time, to skip already solved threads.
    Reply With QuoteRegister To Reply
  3. 02-03-2013, 04:46 PM #3
    JKK123
    JKK123 is offline
    Registered User
    Join Date
    02-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2008 Mac
    Posts
    21

    Re: Dynamic Subtraction

    Hi Ola,

    I've had a go at what you've recommended, but I can't seem to get it to work...

    Firstly - the =IsStrikeThrough(A1) in the B column isn't being recognised.

    Secondly - it won't allow me to input the code you've given into cell C1

    Lastly - I'm not sure if I'm inserting the module correctly? I've got a Mac and am using Excel 2008 - so perhaps there is another way to insert the module?
    Reply With QuoteRegister To Reply
  4. 02-03-2013, 04:57 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Dynamic Subtraction

    Hi JKK and welcome to the forum

    i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 02-03-2013, 05:03 PM #5
    olasa
    olasa is offline
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: Dynamic Subtraction

    Here the Macro for your file.
    //Ola
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-03-2013, 05:35 PM #6
    JKK123
    JKK123 is offline
    Registered User
    Join Date
    02-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2008 Mac
    Posts
    21

    Re: Dynamic Subtraction

    Thanks FDibbins, glad to be here!

    Hopefully what I've uploaded there makes sense. I guess if anyone can reply with that file updated with formulas/macros that solve my problem, that would be great also!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 02-03-2013, 05:53 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Dynamic Subtraction

    There are no regular formulas that will recognize formatting (because it is purely cosmetic).

    The approach I took was to add a column where you can enter a character (I used X), instead of using strikeout.

    I used C for the X, and then in D, copied down, i used this...

    =B1-OFFSET(B1,5+COUNTIF(C1:C6,"X"),0)
    Reply With QuoteRegister To Reply
  8. 02-03-2013, 06:14 PM #8
    JKK123
    JKK123 is offline
    Registered User
    Join Date
    02-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2008 Mac
    Posts
    21

    Re: Dynamic Subtraction

    Thanks so much! That works pretty much exactly as I'd like it to! Thanks FDibbins and also Ola for the help!

    One quick addition - would it be possible for the cell to place an X if the equivalent cell in the other column has an X in it - ie, once C1 has an X in it, then D1 instead of continuing to use the formula and calculating a new result, it places an X in the cell - and if C5 has an X, then D5 also has an X etc...?
    Reply With QuoteRegister To Reply
  9. 02-03-2013, 06:35 PM #9
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Dynamic Subtraction

    try this...
    =IF(C1="X","X",B1-OFFSET(B1,5+COUNTIF(C1:C6,"X"),0))
    Reply With QuoteRegister To Reply
  10. 02-03-2013, 06:50 PM #10
    JKK123
    JKK123 is offline
    Registered User
    Join Date
    02-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2008 Mac
    Posts
    21

    Re: Dynamic Subtraction

    Wow! Yes, now that works absolutely perfectly! Thanks so so much!
    Reply With QuoteRegister To Reply
  11. 02-03-2013, 06:53 PM #11
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Dynamic Subtraction

    Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1