Help w/ Date formula

BMiller828

I am trying to make a formula that inserts the date when a selection is made in a drop down cell in another sheet. For instance, the user selects "IIMC / INSTRUMENT" in the training drop down. I want the date it was selected to fill into the other sheet.

Dropdown.

I want the date to go into this cell on a separate sheet.

Here is the formula I have, but I am getting an unparseable error:

=IF({TRAINING} = "IIMC / INSTRUMENT", {PILOT}PILOT@row, {DATE}, =TODAY())

Paul Newcome

You have misplaced parenthesis and need to finish out the "logical statement" portion of the IF.

=IF(INDEX(..., MATCH(...)) = 1, INDEX(..., MATCH(...)))

Lucas Rayala

Hi @BMiller828, To insert the date when a selection is made in a dropdown cell from one sheet into another sheet in Smartsheet, you can use a combination of the IF and TODAY functions. Here's an example of how you can structure the formula:

```

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

```

In this formula:

- `{Training}` refers to the dropdown cell in the original sheet where the user makes a selection.

- `"IIMC / INSTRUMENT"` is the specific value you want to check against in the dropdown cell.

- `TODAY()` is used to retrieve the current date when the condition is met.

- `""` is an empty string that will be returned when the condition is not met (to leave the cell blank).

To insert the date into the corresponding cell on a separate sheet, you can simply reference the cell in that sheet. Assuming the cell you want to populate with the date is in column A of the separate sheet, you can use the formula:

```

=IF(OriginalSheetName!{Training} = "IIMC / INSTRUMENT", TODAY(), "")

```

Replace "OriginalSheetName" with the actual name of the original sheet.

By using this formula, the date will be automatically inserted into the specified cell in the separate sheet when the user selects "IIMC / INSTRUMENT" from the dropdown cell in the original sheet.

BMiller828

Hello Lucas,

Thanks for the info. Unfortunately, this still doesn't work for me.

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

Gives me an invalid operation error. I shouldn't need the "original sheet name" portion as I am already referencing / linking another cell to the formula. I will also still need the {PILOT}PILOT@row somewhere in the formula as the data has to match with the pilot etc.

Thanks again!

Paul Newcome

You would need to use a Record A Date automation on the source sheet to get the static date when the selection is made.

Then you would need a unique ID for each entry which would allow you to use an INDEX/MATCH to pull the date over.

=INDEX({Recorded Date Column}, MATCH([Unique ID]@row, {Unique ID}, 0))

BMiller828

Thanks Paul. That does match the user name and returns the last date they submit the form. So progress....

I need a way that IF the training column has "IIMC / INSTUMENT" checked, then it returns the date they submit it. I can then use the same formula in the next columns with different " " parameters that need to be met.

Paul Newcome

You would use another INDEX/MATCH to pull in the checkbox and then nest the two in an IF statement.

=IF(index/match for checkbox = 1, index/match for date)

BMiller828

Still stuck on this one. Getting an invalid data type error. Using

=IF({TRAINING}, ="IIMC / INSTRUMENT", INDEX({DATE}, MATCH(PILOT@row, {PILOT}, 0)))

Paul Newcome

You need another INDEX/MATCH. It would basically look the same as the {DATE} but you would be pulling in the checkbox instead. Then you would drop that in the first portion of the IF statement similar to the syntax in my last comment.

BMiller828

Thank you for your help on this. I'm definitely newer to this platform and still learning.

=IF(INDEX({TRAINING}, MATCH("FLIGHT REVIEW", INDEX({DATE}, MATCH(PILOT@row, {PILOT}, 0)))))

Not sure if this is what you mean? Still getting an "incorrect argument" error. I think the issue is that the Training column has multiple checkboxes. The user can select none or all depending on what they did on that flight.

BMiller828

OK. So I can get a "no match" error with - =INDEX({TRAINING}, MATCH(PILOT@row, {TRAINING}, 0))

I can get the date to populate with - =INDEX({DATE}, MATCH(PILOT@row, {PILOT}, 0))

I am having trouble understanding how to index / match the check box? If I index the training column, how to I make the formula match the box that is checked?

Paul Newcome

It looks like you are matching PILOT@row to the {Pilot} range in the working one but looking for the PILOT@row in the {Training} range in the second one.

BMiller828

This kinda worked as it at least returned the checked box. I still need it to be a date though.....

=INDEX({TRAINING}, MATCH("FLIGHT REVIEW", {TRAINING}, 0))

When I try to nest with the IF, still getting errors?

=IF(INDEX({TRAINING}, MATCH("FLIGHT REVIEW", {TRAINING}, 0, INDEX({DATE}, MATCH(PILOT@row, {PILOT}, 0)))))

Paul Newcome

You have misplaced parenthesis and need to finish out the "logical statement" portion of the IF.

=IF(INDEX(..., MATCH(...)) = 1, INDEX(..., MATCH(...)))

BMiller828

Paul I think I'm gonna owe you a beer or pizza or something.....

The formula is working as I am now getting a blank box. I assume, I still have something missing / wrong but not sure what?

=IF(INDEX({TRAINING}, MATCH("FLIGHT REVIEW", {TRAINING})) = 1, INDEX({DATE}, MATCH(PILOT@row, {PILOT}, 0)))

Paul Newcome

Lets start by looking for the exact match in your first INDEX/MATCH similar to how you have done in the second INDEX/MATCH portion. In the third piece of the first MATCH function, use a zero.

BMiller828

I'm guessing you mean like this. I changed the 1 to a 0. I thought that by telling it to MATCH "flight review" it would look for that exact match? Tried this.

=IF(INDEX({TRAINING}, MATCH(FLIGHT REVIEW@row, {TRAINING})) = 0, INDEX({DATE}, MATCH(PILOT@row, {PILOT}, 0)))

This gives me an unparseable error.

Just changing the 1 to a 0 still returns a blank cell, but no errors.

=IF(INDEX({TRAINING}, MATCH("FLIGHT REVIEW", {TRAINING})) = 0, INDEX({DATE}, MATCH(PILOT@row, {PILOT}, 0)))