Enter Date Based On Other Sheet's Checkbox

celtics345

Hello all,

Another formula I'm not equipped enough to handle. All I need is a code that states "when this operator training this discipline has this box checked, enter the date from the reference sheet." Currently, I have a code that enters something close to this, except it's the most recent date, instead of one that's met by a checkbox parameter. That code is listed in the image below. Any help would be greatly appreciated!

Kelly Moore

Hey @celtics345

Try this

=INDEX(COLLECT({Event Date},{Personnel}, Trainee@row, {Discipline}, Qualifier@row,, {Cleared?}, 1),1)

As mentioned earlier, remember you have to create all cross sheet references, you cannot copy paste this into your sheet and use as is.

Does this work for you?

Kelly

Kelly Moore

Hey

Yes, the formula you need is written almost exactly as you stated it. You can have your formula either returning a blank or the Max Date - whichever you prefer. Using IF statements is an important step in mastering formulas.

For a blank

=IF(Training@row=1, "", INDEX(COLLECT({Event Date},{Personnel}, Trainee@row, {Discipline}, Qualifier@row,{Cleared?}, 1),1))

For Max Date

=IF(Training@row=1, MAX(COLLECT({Event Date},{Personnel}, Trainee@row, {Discipline}, Qualifier@row,{Cleared?}, 1)), INDEX(COLLECT({Event Date},{Personnel}, Trainee@row, {Discipline}, Qualifier@row,{Cleared?}, 1),1))

Will either of these work for you?

Kelly

Kelly Moore

Hey @celtics345

I believe you are asking to pull in a date from the row with the checkbox? If this is correct, then that checkbox criteria is needed in the collect. Because a date field can be evaluated as having a Max, you could use a MAX function. If multiple rows exist with this data, the MAX gives you the most recent date. For a way to pull any type of data, an INDEX/COLLECT works.

=INDEX(COLLECT({End Date}, {Personnel}, Trainee@row, {Discipline}, Qualifier@row, {Training}, 1),1)

Don't forget to create the new cross sheet reference for the checkbox field.

Will this work for you?

Kelly

celtics345

Thank you Kelly! We are close, but I did forget to clarify one thing. The checkbox we'd be referencing is in another sheet, not the one visible in that image. The image below is that other sheet. So essentially I'd want that row's Event Date to be entered into the original sheet's Clear Date column when Cleared? is checked.

So, in essence, in the reference sheet, when Trainee + Qualifier are the same and when Cleared? is checked, I'd like that row's Event Date to populate in the original sheet's Clear Date column (the first image). Does that make sense?

For what it's worth, this checkbox will only ever be checked once under those conditions (Trainee + Qualifier).

Kelly Moore

Hey @celtics345

Try this

=INDEX(COLLECT({Event Date},{Personnel}, Trainee@row, {Discipline}, Qualifier@row,, {Cleared?}, 1),1)

As mentioned earlier, remember you have to create all cross sheet references, you cannot copy paste this into your sheet and use as is.

Does this work for you?

Kelly

celtics345

Good news, that works! Initially it was giving me unparseable and I couldn't figure out why. After carefully looking over the code you provided I saw two commas after Qualifer@row. Removing one fixed it. You were testing me I see 😉. I've marked your response as the answer, thank you so much!

Unfortunate news, I didn't consider another element. Is there a way to make an exception for operators currently training? You'll see in the image below your new code works perfectly for cleared operators. However, obviously, I get an Invalid Data response when entering that formula into rows where operators are not yet cleared (meaning the Cleared? checkbox in the reference sheet has not been checked).

Is there a way to add to this formula a rule that states if the Training checkbox is checked (in the primary sheet; see image below) to either 1.) blank out the Clear Date cell, or 2.) use the current max date as before. In either case, the goal is to not include any data from greyed out rows until Cleared? is checked, while simultaneously creating no errors so tabulated numbers will still work. I know I could always manually change the formula when an operator gets cleared, but I'd prefer for everything on this page to be automated.

This may be a messy one, but I trust your acumen!

Kelly Moore

Hey

Yes, the formula you need is written almost exactly as you stated it. You can have your formula either returning a blank or the Max Date - whichever you prefer. Using IF statements is an important step in mastering formulas.

For a blank

=IF(Training@row=1, "", INDEX(COLLECT({Event Date},{Personnel}, Trainee@row, {Discipline}, Qualifier@row,{Cleared?}, 1),1))

For Max Date

=IF(Training@row=1, MAX(COLLECT({Event Date},{Personnel}, Trainee@row, {Discipline}, Qualifier@row,{Cleared?}, 1)), INDEX(COLLECT({Event Date},{Personnel}, Trainee@row, {Discipline}, Qualifier@row,{Cleared?}, 1),1))

Will either of these work for you?

Kelly

celtics345

That is incredible Kelly, thank you! Both of those worked perfectly. Something slight clicked in my head in regards to the IF statements. I'll still need lots of practice, but I think I'm understanding a bit how you can string statements together to solve whatever you want. This was immensely helpful, I really appreciate it!

blmccue

@Kelly Moore , I am trying to do something similar to the above but I want to collect the minimum date if another column has Yes in it.

This is the sheet I am referencing and if the CDL Major Offenses = Yes then I'd like to have the oldest date form the date column. I've tried =IF({New Daily Reports to Process Range 1} = "yes", MIN(COLLECT({New Daily Reports to Process Range 1})), TODAY()). If false then I would like it to return today's date. Any suggestions?