Predict or set which row a form writes to
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!