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:

指数(Col1 Form1中Data#:[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

✅了我的帖子(s)帮助或回答哟ur question or solve your problem? Please support the Community bymarking 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.