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. 🤞

Freymish

Interesting. Unfortunately, I am developing this for a group of people, and I can't assume they won't be doing all kinds of crazy stuff with the sheet. :)

KPH

😕 Oh dear!

I don't have a solution for you, I'm afraid. Good luck! 🤞

Andrée Starå

Hi @Freymish

I hope you're well and safe!

You could have the form populated at the top of the sheet instead.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Freymish

Well, as usual, I overthought this one! Here's how I did it:

I'm still not happy with hard coding the range but I think I can live with it.

=VLOOKUP("MyStringVal", Area100:[Col27]150, 2, 0)

=VLOOKUP("MyStringVal", Area100:[Col27]150, 3, 0)

=VLOOKUP("MyStringVal", Area100:[Col27]150, 4, 0)

...

Andrée Starå

@Freymish

There might be a better solution.

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Freymish

I tried your suggestion of having the form deliver to the top of the sheet and I end up having the same issue. Since the form 'inserts' a line, it changes the formulas below it which means the data I need is above the defined search range.

The formula is essentially the same except the range is different

The Customer Payment Contact Name value is gathered via this:

=VLOOKUP("Answer1", Area1:[Col27]5, 2, 0)

In the case below it works because I fixed the formula and added a row above the form return row. If another form reports in data it will break that forms returns because the VLOOKUP range will change from:

[Area]1:[Col27]5

to

[Area]2:[Col27]6

and the new data will be on row 1