Is there a way to create a range in a formula that never changes, even if rows are inserted/deleted?

Freymish

I have a sheet I'm working on that pulls data from form responses. I don't want to have to pull from another sheet if I can help it. I have a lookup like this:

=IFERROR(VLOOKUP("Form1Answers", [Col]1:[Col25]5, 3, 0), "")

This works fine until the form returns data and inserts the new line. When that happens, because the formula is below this, the formula increments to:

=IFERROR(VLOOKUP("Form1Answers", [Col]2:[Col25]6, 3, 0), "")

and misses the data. I don't want to have to fix the formula every time of course.

Is there a way to set a formula so that it never changes? I assume I could use the Summary variable for each value to do this but that would be pretty annoying. ;)

I had a similar issue with the form data going to the bottom of the sheet since I can't be sure exactly where it will land, and I can't create a range that exceeds the last row in the sheet. is there a secret cheat code for specifying the first row in the sheet?

Paul Newcome

You can use something like

Col:[Col25]

This will include all cells in all of those columns.

Freymish

That creates a circular reference since it contains the cell that the formula is in.

JamesB

@Freymish Add a $ sign to your formula.

=IFERROR(VLOOKUP("Form1Answers", [Col]$1:[Col25]5, 3, 0), "")

This will keep the first part of the formula as 1 but increment the second as you pull the formula down.

Freymish

Hmm.. I just tried that, and it didn't work. It still increments the row number in the range definition when the form inserts the new row at the top of the sheet.

Reading up a little and trying.. It works if I cut and paste, or copy the cell with the formula to another location. So, it is static in respect to its original location, but not if I add a row above it. In that case it still increments the row numbers in the defined range. Rats!

Kelly Moore

Hey @Freymish

I've done this with a helper column and then using INDEX/COLLECT so I can bring in my helper column criteria. It uses the system autonumber column [Row ID] so that must be present, if not already.

I typically name my helper column Row ID Display - this helper mimics the Row number displayed on the left hand of the sheet. The top row is always row #1 on that display. (The bottom row is always the max([Row ID Display]:[Row ID Display]).

Row ID Display=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

Then your INDEX/COLLECT becomes

=INDEX(COLLECT([the range you are trying to collect]:[the range you are trying to collect],[Form Answers column]:[Form Answers column], "Form1Answers", [Row ID Display]:[Row ID Display], @cell>1),1)

*if you are wanting to limit the range to only row 25 then you could add another criteria where the @cell<=25.

Would this approach work for you?

Kelly

Paul Newcome

Have you tried putting the reference table in some helper columns so that you can use the full column references?