Excel Table - Array Formula Disrupted by Adding New Rows

[JE365]

I'm having trouble with inconsistent calculated formulas when adding new rows in a table in Excel. For context, I adapted this video on how to create a searchable drop down list in Excel for a project I am working on. https://www.youtube.com/watch?v=vkPoViUhkxU

I'm designing a template which will be sent to other users and they will input their own data every quarter, so I need it to work properly as is instead of correcting formulas after data is added. The two columns that are giving me problems both have array formulas in them. They are both array formulas that include a fixed cell at the top of the column and then the bottom row of the array expands down the column. Basically, users enter data into columns B-E. To the right of a table (in cell J6) is a searchable drop down list. When something is typed into this cell, the formula in Column A updates to be a sequential count of all of the matches, with nonmatches being 0. Column G is a list of all of the matches and is the dynamic range populating the drop down list.

Column A: =IF(ISNUMBER(SEARCH($J$6,C:C&" "&D:D)),MAX($A$4:A4)+1,0)
Column G: =IFERROR(VLOOKUP(ROWS($G$5:G5),A:H,6,0),"")

Here's my problem:

When I add new rows to the bottom of the table (whether I just paste new data or try and resize my table) the row that was previously the last row of the table almost acts as if it's the new last row of the table. For example, if cell G14 was the last row of the table prior to adding new rows (Row 15-31) the formula in G changes from:

=IFERROR(VLOOKUP(ROWS($G$5:G14),A:H,6,0),"")
to
=IFERROR(VLOOKUP(ROWS($G$5:G31),A:H,6,0),"")

The same problem is happening in column A, but at row 15. It changes from:

=IF(ISNUMBER(SEARCH($J$6,C:C&" "&D:D)),MAX($A$4:A14)+1,0)
to
=IF(ISNUMBER(SEARCH($J$6,C:C&" "&D:D)),MAX($A$4:A31)+1,0)

The problem only occurs in those rows and returns to normal afterwards, but does not work properly because of the disruption.

I'm posting a screenshot of when it is working properly after I correct the errors, and a screenshot of what it looks like with the errors. I am sending the template to users with minimum Excel experience and columns A,F,and G will be hidden, so I want the template to function correctly without needing to manually correct the errors after inputting new data.

Thank you for any insight into why the array formulas are being disturbed by new rows and how to avoid this!

 

[SpillerBD]

If you are using a Table, then you should be using the Table Field references.

 

[JE365]

How do I use Table Field references to refer to all of the cells above a given cell in the column and not the entire column? ex: in cell A20, Max(A:A19) and not max of all of column A?

I know this isn't the intended use for tables, but I wanted to put it in a table so that the formulas in the hidden columns would autofill as new data was entered so that users could enter anywhere from 5-10,000 rows without me having to make a large file to begin with and pre-populate the formulas.

I also still don't understand why this error is happening. Why is the previous last row of the table still acting like the last row even when new rows are inserted?

Thank you for your help!

 

[SpillerBD]

I also still don't understand why this error is happening. Why is the previous last row of the table still acting like the last row even when new rows are inserted?

Because when Excel tries to figure out what the consistent formula should be for the field (ie column) you have it confused. The other problem, though you might not plan to ever incur it but Microsoft guards against it, if you were to sort your table what could happen to those range references? Oh yeah, major mess potential considering those formulas require contiguous ranges.

I haven't dissected your formula but this is the type of thing Mike Girvin and his understanding of Array formulas can be wow.
Using ROW() or INDIRECT... may be part of your solution set.

 

[jedimatt]

Using ROW() or INDIRECT... may be part of your solution set.

I realize this is an old thread, but there isn't an answer here yet, and I just figured it out. SpillerBD was correct that using ROW() helps. You also should use INDEX along with it.

Step 1: Give your table a name. While the cursor is in your table, click the Design tab and in the Table Name field, give it a name. I'll call your table MyTable.

Step 2: This is optional, but helpful. Give defined names to any column indices where you plan to use only part of the column in formulas. I use defined names for these to keep the formulas cleaner and easier to understand. For this, go to the Formula tab and use the Name Manager. You can give a formula a name here. Normally, I name these similarly to how the column headers are named (like DateColumn or AmountColumn), but because I don't know your headers, I will just call your column A "AColumn" and your column G "GColumn". AColumn would be defined as:
=COLUMN(MyTable[Column A's Header Name Here])

This will be just a number, by the way, not the column itself. We want this to be the column number within the table so we can use it to locate the column later. This formula assumes your table starts in the first column of your spreadsheet (that is, column A, which obviously yours does). If your table was to the right of that, you would need to subtract however many rows to the right your table was.
GColumn would be defined similarly as:
=COLUMN(MyTable[Column G's Header Name Here])

Step 3: For a dynamic range within a formula including only the part of AColumn up to the current row, use the following:
INDEX(MyTable, 1, AColumn):INDEX(MyTable, ROW()-1, AColumn)
The "ROW()-1" part assumes two things. (1) It assumes your table starts in the first row of your spreadsheet. If not, you would need to subtract however many rows your table is below that. (2) It assumes your table has headers. Row index 1 will refer to the first data row, which would be row 2 in the spreadsheet if you have headers. So calling ROW()-1 in cell A2 will result in a value of 1, which is what we want so we can reference it as the first data row of the table.

Now, assuming your table starts in row 1 and that you have a header row, then $A$4 will be INDEX(MyTable, 3, AColumn) and $G$5 will be INDEX(MyTable, 4, GColumn). Applying this to your formulas results in the following:

Column A: =IF(ISNUMBER(SEARCH($J$6,C:C&" "&D:D)),MAX(INDEX(MyTable, 3, AColumn):INDEX(MyTable, ROW()-1, AColumn))+1,0)
Column G: =IFERROR(VLOOKUP(ROWS(INDEX(MyTable, 4, GColumn):INDEX(MyTable, ROW()-1, GColumn),A:H,6,0),"")

And now new rows can be added to the table without messing up these formulas.