Named range to get the block of cells above the current cell - rewrite to avoid circular reference

Question

OK, this is a problem that has been nagging me for a while and I really though that I had cracked it, but ...

Consider the following worksheet:

Spreadsheet

The Data cells just contain the number 1 and the Sum cells contain the formula shown. This all works.

It makes use of the following names (with explanations):

RangeAboveAll
=INDIRECT(CONCATENATE(ADDRESS(1,COLUMN()),":",ADDRESS(ROW()-1,COLUMN())))

The 1st ADDRESS function gives the address of the first cell of the current column, the second ADDRESS function gives the cell above the current cell. These are CONCATENATEd with a : and passed to the INDIRECT function to turn it into a range reference. Note that this will give an error if used in the top row, as it should.

BlankRowAbove
=IFERROR(SMALL(IF(ISBLANK(RangeAboveAll),ROW(RangeAboveAll),""),
COUNTIF(RangeAboveAll,"")),0)

The IF function creates a list corresponding to the RangeAboveAll with the column number if the cell ISBLANK and 0 if it isn't. This is then passed to the SMALL function with the COUNTIF argument that counts the number of blank cells; therefore, SMALL returns the column number of the last blank cell. This is then wrapped in an IFERROR to deal with the case where there are no blank cells.

RangeAboveBlock
=INDIRECT(CONCATENATE(ADDRESS(1+BlankRowAbove,COLUMN()),":",
ADDRESS(ROW()-1,COLUMN())))

Using the same technique as the RangeAboveAll name, this takes the column of the last blank cell (or 0 if there isn't one) and returns the range starting 1 below this and ending 1 above the current cell. If the cell immediately above the current cell is blank then this creates a circular reference but this is fine.

As I said, all of this works fine UNTIL one of the data cells in the top block refers to the result of the bottom block. For example, if Cell B1 had the formula =B10.

Now if this was being done without the dynamic ranges this would NOT be a circular reference but because the dynamic range has to calculate all the cells above simply to exclude them, the circular reference pops up.

This is a particular problem because we have a spreadsheet that starts with a summary of what is going on below. Now it could be fixed by turning on iteration but that leaves us open to other problems. It can also be implemented in code but I don't want to have to enable macros just for this.

Can anyone see a way to build these sort of named ranges that avoids this problem?

For background information, there is a specific problem (user error) that this is trying to overcome. If you have a static formula say =SUBTOTAL(B7:B12,9) in B13 and you insert a row either above B7 or below B12, this new row will be outside the sum range. Since we use this spreadsheet as a template for preparing claims for construction work on many different projects, each of which has a different number of rows in each subsection, leaving a line item out worth tens of thousands of dollars is a problem.

Thanks @pnuts - the Sum was just used as an example it could equally use a subtotal and the same issue would arise. I will edit the question to show exactly what I mean. — Dale M

pnuts pnuts · Accepted Answer · 2013-08-23T07:47:25

May not suit but I need more room than in a comment!

If you data starts off as on the left (I have inserted ColumnA to provide 'grouping rules' for Subtotal) and you apply Subtotal the result can be as on the right:

SO18395841 example

The relevant part being that C2 (ie B2 shifted one column to the right) does contain a formula referring to cells below it (specifically, what was B10, ie was the sum of B8 & B9).

Use of Subtotal, without iteration turned on has not generated a circular reference. (The formula automatically adjusted to =SUM(C8:C9).

Mind you, I guess much the same could be achieved 'your way' - instead of =B10 use =B8+B9.

Named range to get the block of cells above the current cell - rewrite to avoid circular reference

2 Answers