Predict or set which row a form writes to

Freymish

I have a sheet that I am using to gather data from several different sources. I will need to create at least two forms for this which I will use to populate the top part of the Sheet. I am using an Index/Match to go to the row data that I need to pull from the forms responses, and that works great.

Here's the issue:

When a form returns data it writes it 10 or 11 rows below the last row with data in it. If I only had one form that wouldn't be an issue, but because I have a few reporting back to the sheet, I get responses on several lines under the main body of the sheet. This presents a problem when building the Index/Match.

I can identify the row # the string is on (I made sure it's unique) and store that value in a sheet summary var Like this:

Form1Data =MATCH("Some Unique Str", Col1:Col1, 0)

What I would like to do is use that value to set the range for the index from col1 : ColX and then grab the value from the list by its position. Something like this:

INDEX([Col1]Form1Data#:[ColX]Form1Data#, [Form1Data]#, 2)

When I attempt to use the var, whose value is a number, as a row number it fails. Is there a way to create a dynamic range built off an identifiable, but not set row number?

I hope this makes sense. Even I'm confused!

KPH

Hi @Freymish

I don't have the answer to your formula question. However, you might not need it. The form should populate the new row directly below the last used row in your sheet. I've only had issues like you describe, where 10 blank rows are between entries, if I've been in the sheet and touched a cell in the 10 blank rows that are always there at the very end. It is as if Smartsheet recognizes them as "used" and then skips over them and puts the new data below. If you don't click in them at all, you could be fine. 🤞