Auto fill date from forms

BMiller828
BMiller828 ✭✭
edited 06/28/23 in Formulas and Functions

I am trying to set up a formula that pulls data from a form / sheet to a master sheet. When a user fills out a form (see pic)


I would like the date to auto populate in the corresponding column on a different sheet.


Example: The "flight review" box is checked on the form and submitted. On the master sheet, the date the form was submitted is filled into that column. It would be helpful if it matched the user name in the "pilot" box as well. Thx.

Best Answer

Answers

  • Adriane Price
    Adriane Price ✭✭✭✭✭✭

    @BMiller828 - Hello

    You could take the cells you want to update on the different sheet and use the "Link from Cell in Other Sheet..." or Data Mesh both of these options you would need a unique ID for each sheet to grab the data you want from the master sheet row to be updated in the different sheet row.

    If it is a vast amount of data you could also create a VLOOKUP or INDEX/MATCH formula to connect the data from both sheets.

    If not wanting to use formulas data mesh would work, it would allow you to automatically populate empty cells in one sheet based on a known lookup value (unique identifier) in one or more sheet.

    Adriane

  • OK. I am somewhat familiar with linking cells from another sheet. I am still unsure of what the actual formula would be to make the date auto populate when the corresponding box is checked on the form?

  • So here is what I am trying and getting various errors:

    =IF({TRAINING}, ="IIMC / INSTRUMENT", TODAY())

    Should the cell be a text field or a date field?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    If you're using TODAY(), then the column needs to be a date, else you will get a #INVALID COLUMN VALUE error.

  • Thanks. Still haven't figured this one out exactly. Appreciate the info!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    TODAY() may have a downside though - it will update to the current day.

    If your data sheet has a created date column, I would either use a VLOOKUP or INDEX/MATCH to grab this or set up a fairly basic automation to autofill today's date in the relevant column (which will put the current date but this will be frozen).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!