Help with referencing data that is input through a form

A&W Admin

Hi all,

I'm hoping someone can help me with my specific issue that I cannot seem to resolve. In our Workspace that is set up through control centre, we have a sheet called a Checklist that is populated for each of our provisioned projects. The information in the sheet is populated via a form, as there is around 50 columns worth of inputs (i.e. too many to scroll along a page and input by hand). My issue is that I want to report 3 of these data points to our metadata sheet for Rollup reports. Right now I have a summary section with these 3 fields using an IF formula to figure out which row the form populated into so that the information can be reported to the metadata (see screenshot and formula below). However, because of the formula, the form entry is populating on the row below my formula, making it useless. We had an index/match formula before which also didn't work. Does anyone have a suggestion on how they've set this up in your environment?

Some other notes: we do have a check box column that's hidden, which alerts our Legal department when a form is completed so they can do a review.

This is the formula I'm currently using (utilizing the checkbox mentioned):

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

Adam Murphy

Your screenshot does not align with your formula (you are referencing 2 columns which you do not display in the screenshot…and none of the columns that are actually IN the screenshot). This makes it challenging to help you. What specifically are you trying to calculate and where are you putting your formula(s)?

Sorry if I am just misunderstanding, but I don't see enough details to assist.

A&W Admin

You're correct, these columns are faaarrr down the sheet. Here's a new screenshot with the columns visible.