CODEWITHSUNDEEP
exceldynamicsubtraction
user2037040
user2037040

Reputation: 1

Dynamic Excel 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?

Upvotes: 0

Views: 1322

Answers (1)

Peter L.
Peter L.

Reputation: 7304

(EDIT#1: misread the input, sorry)

A bit straightforward, but will do the job: type =A1-INDIRECT("A"&SMALL(IF(A:A<>"",ROW(A:A),""),6)) and press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in {} brackets around it (but do NOT type them manually!).

To speed up calculation you may replace A:A to any limited range.

Sample file (resulting formula is yellow-marked): https://www.dropbox.com/s/sy7zkg71xtfgib9/Subtract5th.xlsx

(EDIT#2: misread the "strike-out", sorry)

Font styles (as well as similar cell properties) may NOT be read by default Excel functions, that's why you need to add UDF called StrikeOut:

  1. Press ALT-F11 - thiss will open VBA editor.
  2. Insert new module: Insert > Module.

  3. Paste the code to added module:

    Function StrikeOut(R As Range) As Long
Dim c As Range
StrikeOut = 0
For Each c In R.Cells
    If c.Font.Strikethrough = True Then StrikeOut = StrikeOut + 1
Next
End Function

  4. Add the formula to B1: =A1-INDIRECT("A"&(6+StrikeOut(A2:A10)))

  5. Set strikethrough font to any cells in A1:A10.
  6. Unfortunately, cell format change does NOT trigger any change event, so you need either press F9 or change any cell value on the sheet to recalculate and therefore update result in B1.

Sample file is shared: https://www.dropbox.com/s/n9o7tn3ks3x8nza/StrikeOut.xlsm

P.S. at least for me that was extremely useful)))

Upvotes: 1

Related Questions