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?
Answers
-
You can use something like
Col:[Col25]
This will include all cells in all of those columns.
-
That creates a circular reference since it contains the cell that the formula is in.
-
@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.
-
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!
-
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
-
Have you tried putting the reference table in some helper columns so that you can use the full column references?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 361 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!