Formula for checkbox if meets criteria

Christina09

Hi,

I've kind of asked this question before, but the formula that was suggested doesn't work on multiple dates.

I have sheet A and Sheet B

Sheet A has columns of ID# and Date

Sheet B has columns of ID#, Date 1, Date 2, Date 3 etc...

The formula I have in sheet B didn't work when there is a different date column in the same sheet.

=IFERROR(IF(DATE(2021, 8, 26) = INDEX({sheet A date complete}, MATCH([ID #]@row, {Demo Sheet A ID}, 0)), 1), "")

Sheet A: https://app.smartsheet.com/b/publish?EQBCT=bb177bd5a51d4634ba15073fc5899d33

Sheet B: https://app.smartsheet.com/b/publish?EQBCT=50702c2c7125446aafd4271eddff6a9f

Does anyone have a solution for this?

Thank you in advance,

Christina

Paul Newcome

What exactly are you wanting the formula to accomplish?

Genevieve P.

Hi @Christina Lam

Is this your other post? If so, it looks like you were on the right track with the INDEX(COLLECT train of thought. This will allow you to search for two matches: both the ID and the Date, before checking the box.

Try this:

=IF(INDEX(COLLECT({sheet A date complete}, {sheet A date complete}, DATE(2021, 8, 27), {Demo Sheet A ID}, =[ID #]@row), 1) = DATE(2021, 8, 27), 1, 0)

I put an = before the [ID #]@row because some of your numbers begin with a 0, so this helps it find a match.

I put this in your second column in the test sheet above. Let me know if this makes sense or if you'd like me to break down the formula at all.

Cheers,

Genevieve

Christina09

Hi @Genevieve P. ,

Thanks, I saw the formula in the second column. It seems to work on the demo sheets, but not on the actual sheet I'll be planning on using. I got the #invalid value error and couldn't figure out why.

I have two different sheets below with the formula entered: one with a date column to match, and the other one I'll have to type in the date in the formula to match.

Thanks,

Christina

Christina09

Hi @Paul Newcome,

The formula needs to match the ID# and date on Demo Sheet A and then check the box in Demo Sheet B when it does.

Hopes this helps.

Thanks,

Christina

Paul Newcome

If you just need to make sure that there is a match on the date and id, then my suggestion would be more along the lines of...

=IF(COUNTIFS({Other Sheet Date Column}, @cell = Date@row, {Other Sheet ID Column}, @cell = [Badge #]@row) > 0, 1)