Is there a way to create a range in a formula that never changes, even if rows are inserted/deleted?
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?
Help Article Resources
Check out the Formula Handbook template!