Excel SUM across sheets: determine row based on current cell

1
votes

Consider this formula, which sums the content of cells A6 in the worksheet range from RbStart to RbEnd.

=SUM(RbStart:RbEnd!A6)

Is there a way to make A6 dynamic in the sense that it takes the row number from the current cell's row? The reason is ask is that my first sheet is a sheet that consolidates a dynamic number of other sheets, located between RbStart and RbEnd (RbStart and RbEnd are just helper sheets to be able to include a dynamic number of sheets.)

The rows on the first sheet are dynamic. This is OK because Excel will automatically update the cell reference when you add additional rows. The problem is that for technical reasons during row generation we have blank rows in between, which are removed afterwards. But when you do that, Excel does not update the formula, so it goes out of sync with the dynamic sheets it sums.

You can simulate this as follows:

  1. Put my formula in cell A6.
  2. Drag it down to copy. Note how in subsequent rows, it becomes A7, A8, A9, A10, etc.
  3. Now delete, say, row 9. Note how formula in row 9 no refers A10.

Since the same blank rows are deleted on the dynamic sheets, it now refers the wrong row. If I could somehow change the SUM formula such that instead of A6 it takes the row that the current cell is on, the problem would be solved.

I hope I am making sense. Happy to clarify.

1
excel-formula
Check out the indirect formula support.office.com/en-us/article/… – user2023861
I tried that, but I could not get it to work. – Roel Vlemmings

1 Answers

1
votes

Well the first thing that came in my mind was to use below formula

=SUM(INDIRECT("'Sheet1:Sheet3'!A"&ROW()))

But then I realized INDIRECT does not work here.

So you'll have to list down names of all the sheets somewhere and refer that list in formula. Using named range will be a better option here. See this link for details on named range. You can then add/remove sheet names in this list as and when required. After that you can use the following formula 

=SUMPRODUCT(SUMIF(INDIRECT("'" & NamedRange &"'!A" & ROW()),"<>0"))

or you can use below array formula

=SUM(N(INDIRECT(NamedRange & "!A"&ROW())))

Being an array formula, commit it by pressing Ctrl+Shift+Enter.

Instead of named range you can also use cell range directly. Assuming you have list of sheet names in Cell AA1:AA5 then formula becomes

SUMPRODUCT(SUMIF(INDIRECT("'" & $AA$1:$AA$5 &"'!A" & ROW()),"<>0"))

Other option would be to use VBA.