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

Freymish
Freymish ✭✭✭
edited 11/09/23 in Formulas and Functions

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?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!