Form with formula INDEX/MATCH

Claire Gauvreau

Hello!

I'm trying to see if this is possible. I'm creating a calibration form, but I'd like a specific task duration to be tied to each item, which then populates the due date.

For example:

• Each piece of equipment has a set duration (ie. 365 days). This would be hidden on the form sheet.
• Once a user submits the form, I'd like the form sheet to link the duration from an intake sheet, and that would determine a due/completion date

Is that possible with a formula and a INDEX/MATCH look up? Thank you!

Genevieve P.

Hello Claire,

Yes, this is possible with an INDEX(MATCH! The way I would do this is to have a second, separate sheet that is just the chart for each Item & its number of days.

Then you wouldn't include the field in the form at all, but instead have the Index Match in your sheet using cross sheet references. An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}))

Now, the only thing is, this couldn't be linked to your Project Settings or Gantt Chart, since the Dependency column in Gantt charts is unable to retain a formula. However, you could update a regular Date column to find out the finish date, by adding the number to another date column (either a Start Date column that was filled out in the form, or a System Created Date column that shows when the row was created).

This would just be [Created Date]@row + Requirements@row

I would suggest just having the number in your Duration column, without the "d", so that you can add it easier to the Created Date. You can read more about adding days to dates in our Help Center (here).

Let me know if this makes sense, or if you have any questions!

Cheers,

Genevieve