# Automatically deduct strikethrough from total

Status
Not open for further replies.

#### Akwaman

##### New Member
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.

#### Attachments

• Futurecast Bills.xlsx
41.6 KB · Views: 12
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 …​

I'm sorry. I'm not following.

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.

#### Attachments

• SumStrikeThrough(BY).xlsm
46.9 KB · Views: 19
That was clever.

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!!!!!!!!

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

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.

Status
Not open for further replies.