Collect an column entry when data is entered by a form

A&W Admin

Hi,

I'm still tearing my hair out over this issue, and I'm hoping someone can help me.

In our set up that is run through control centre, we have specific information that is entered onto a sheet via a form. I need to link the information that is entered into that form back to the metadata for each project. The issue I'm running into is that I enter either an INDEX or IF formula, when I then use the form to input the data the form data enters into the row below the one that is referenced in the formula.

The screenshot of the sheet is shown below. We have a summary section and I want to collect the "Location Consultant Fee". When the form is submitted, it automatically adds a checkbox into the "check" column, so I wanted to use that as the reference for which row to return. Note that only 1 form is ever submitted into this sheet, so we don't need to worry about multiple entries. The screen shot also shows the formula I used. I also tried the following IF formula, which ultimately has the same result:

=IF(Check20 = 1, [Fixturing Period (Days)]20, IF(Check19 = 1, [Fixturing Period (Days)]19, IF(Check18 = 1, [Fixturing Period (Days)]18, IF(Check17 = 1, [Fixturing Period (Days)]17, IF(Check16 = 1, [Fixturing Period (Days)]16, IF(Check15 = 1, [Fixturing Period (Days)]15, IF(Check14 = 1, [Fixturing Period (Days)]14, IF(Check13 = 1, [Fixturing Period (Days)]13, IF(Check12 = 1, [Fixturing Period (Days)]12, "")))))))))

Does anyone have a suggestion on how I can report the Location Consultant Fee back to the metadata for this project. I'm willing to rework the sheet if the current set up is not ideal.

SSFeatures

Hi @A&W Admin, I think you can accomplish this by using an INDEX + MATCH formula.

For example:

=INDEX([Location Consultant Fees]:[Location Consultant Fees], MATCH(true, Check:Check, 0))

This will return you the value in Location Consultant Fees for the row where Check is equal to true. In other words, it'll give you the location consultant fee for the row with the check box clicked.

Notice how in my screenshot, my summary value is 100, because it's grabbing the value from the row where the checkbox is clicked.

Hope this helps!

SSFeatures

Hi @A&W Admin, I think you can accomplish this by using an INDEX + MATCH formula.

For example:

=INDEX([Location Consultant Fees]:[Location Consultant Fees], MATCH(true, Check:Check, 0))

This will return you the value in Location Consultant Fees for the row where Check is equal to true. In other words, it'll give you the location consultant fee for the row with the check box clicked.

Notice how in my screenshot, my summary value is 100, because it's grabbing the value from the row where the checkbox is clicked.

Hope this helps!

A&W Admin

Thank you, thank you! It seems to have worked, at least in my test sheet. You have saved me many hours of cursing at the computer!

Andrée Starå

https://community.smartsheet.com/discussion/139554/collect-an-column-entry-when-data-is-entered-by-a-form

Hi,

I hope you're well and safe!

Would there always only be one value in the Location Consultant Fee column?

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me 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! I appreciate it, thanks!

SSFeatures

@A&W Admin You're welcome! I'm glad it worked!

A&W Admin

@Andrée Starå - 2-3 times a year we may have multiple forms for a single site. If you have a simple solution I'd love to hear it. To be honest, since the number of times multiple forms will be submitted is very low I was going to leave this edge case to manual intervention.

A&W Admin

In case anyone else finds this, I modified the formula so that if there's an error the cell is blank:

=IFERROR(INDEX([Location Consultant Fees]:[Location Consultant Fees], MATCH(true, Check:Check, 0)), "")

Andrée Starå

https://community.smartsheet.com/discussion/comment/486078#Comment_486078

Excellent!

✅ Remember! Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏

Paul Newcome

You could try this to cover when there are multiple forms submitted…

=INDEX(COLLECT([Location Consultant Fees]:[Location Consultant Fees], [Location Consultant Fees]:[Location Consultant Fees], @cell <> ""), COUNTIFS([Location Consultant Fees]:[Location Consultant Fees], @cell <> "") + 1)

Of course an IFERROR would be in order for when no forms have been submitted yet, but I usually don't add that until after I've made sure the base formula is working.