Need Strikethrough To Subtract Value From Subtotal
I have a situation where Cell A1 displays the subtotal of Cells A2 through A10:
Cell A1=100
Cell A2=20
Cell A3=30
Cell A4=40
Cell A10=10
I need the subtotal cell to subtract any of the values that get formatted with the Strikethrough feature:
Cell A1=60
Cell A2=20
Cell A3=30
Cell A4=40 (this value receiving the strikethrough)
Cell A10=10
How can this be done?
Last edited by swordswinger710; 03-06-2012 at 03:25 PM.
There is so much good in the worst of us,
And so much bad in the best of us,
That it hardly behooves any of us
To talk about the rest of us.
Try UDF
Please Login or Register to view this content.
UDF Definition:
1. Universal Disk Format
2. Ultra Deep Field
3. United Dairy Farmers
4. User-Defined Functions
I'm assuming you're talking about definition number four? How do I go about doing that, if you don't mind my asking?
Alt + F11 to open the VB window, insert a module, then copy and paste the code in the module. Yeah, I meant #4 UDF
Ah, I knew how to do that, I just wasn't familiar with the term.Lol, anyhow, I did that, and nothing much seems to be happening when I do a strikethrough as of yet.
Does anyone know how I could fix this code?
Works fine for me
In the worksheet Module insert this . For some reason when you format the font the formula won't calculate.
Please Login or Register to view this content.
Last edited by JieJenn; 02-28-2012 at 04:01 PM.
Ah! I see how you did that, I was pasting your code in the sheet code. My bad. Is there a way to force your A6 Cell to refresh after something is crossed out? Right now it only displays the right number after I double click and then exit a cell.
I am trying to figure that out too....... haha........ Only way I can think of is insert this code to the worksheet module
Please Login or Register to view this content.
Hmm, did that work for you? I'm still having issues with it. Or maybe it's because I'm pasting your last code in the wrong place again, I'm not sure... but for whatever reason, I still have to double-click a cell to make it work.
I think it "works" fine for me.
This seems to be exactly the same as your first attachment.. I must be missing something. Where did you paste your last code?
Sheet1 worksheet module
Oh I see. I attached the wrong file
I really suggest you don't do this. As you have found, changing formats does not trigger a recalculation. I would suggest you use an additional column into which you type say an X, then have conditional formatting strike the number out. You can then use the X data in a simple SUMIF formula that will always be accurate.
Good luck.
Well, that does seem to work now, thanks to JieJenn's latest post, but that's good advice OnErrorGoTo0, thank you.
I'm having a small issue with trying to add another group of cells to be included in the subtotal. How would I go about editing the formula so that this will work?
...and this has been solved here. Thanks so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks