Automatically deduct strikethrough from total

[Akwaman]

Hi All. I've been racking my brain trying to figure this little formula out. I have a bill spreadsheet that I track over the course of a month. I would like to be able to see the balance of the total bill decrease each time I strike through a cell when I receive a confirmation email that it has been deducted or processed. I've tried to write a macro with not luck, and now I'm just looking for a basic deduction formula. I've attached the file and the total is located at C17 and eventually through the month, all of the column C items would be stricken through (see past months as an example). Any help you give will be greatly appreciated. Stretch goal, a macro would be nice, but for the immediate satisfaction, seeing the balance deduct after an item is stricken through would be ecstatic! Thanks in advance.

 

[Marc L]

Hi, as your attachment can very not handle any VBA procedure​

so first use the Code option in the 3 dots icon (Insert) to post your code attempt​

and well elaborate at least in order there is nothing to guess when you 'strike through a cell'​

- as VBA can't detect that then should be done via a worksheet event like Right Click for example -​

what must be exactly achieved …​

​

A smart no VBA way is obviously using an additional column to mark a row​

and using the condifional formatting to 'strike through' and applying total according to this additional column …​

​

 

[Akwaman]

I'm sorry. I'm not following.

 

[bosco_yip]

Formula Solution with an old school Excel GetCell function

1] Create a range name:

• Select C18 >> Formulas >> Define Name >>
• Name : SumStrikethrough
• Refers to : =-SUM(GET.CELL(23,IF(1,+OFFSET($C$2:$C$16,ROW($C$2:$C$16)-MIN(ROW($C$2:$C$16)),)))*$C$2:$C$16)
• OK >> Finish

Then,

2] Enter in cell C18:

=SumStrikethrough

3] Enter in cell C19, formula:

=SUM(C17:C18)

Remark : GetCell is a Macro 4 function file need to be saved in xlsm type Excel Macro -Enabled Worksheet

Press F9 for sheet refresh while each time you strike through a cell amount.

 

[rlv01]

That was clever.

 

[Akwaman]

Formula Solution with an old school Excel GetCell function

1] Create a range name:

• Select C18 >> Formulas >> Define Name >>
• Name : SumStrikethrough
• Refers to : =-SUM(GET.CELL(23,IF(1,+OFFSET($C$2:$C$16,ROW($C$2:$C$16)-MIN(ROW($C$2:$C$16)),)))*$C$2:$C$16)
• OK >> Finish

Then,

2] Enter in cell C18:

=SumStrikethrough

3] Enter in cell C19, formula:

=SUM(C17:C18)

Remark : GetCell is a Macro 4 function file need to be saved in xlsm type Excel Macro -Enabled Worksheet

Press F9 for sheet refresh while each time you strike through a cell amount.

View attachment 83857

This was simply BRILLIANT!!!!!! Oh! My! GOSH!!!! Thank you SOOOOOO much for your help with this!!!!!!!!

 

[Promyse43]

This works in the first column but when I try to create a second Formula for a second column It doesn't work.

 

[Luke M]

This works in the first column but when I try to create a second Formula for a second column It doesn't work.

Hi Promyese43. I'd recommend starting your own thread with the question, rather than jumping on a thread that is more than a year old.