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

Options
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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You can use something like

    Col:[Col25]


    This will include all cells in all of those columns.

  • Freymish
    Options

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

  • JamesB
    JamesB ✭✭✭✭✭✭
    Options

    @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
    Freymish ✭✭
    edited 11/10/23
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!