# Formula for checkbox if meets criteria

Options
✭✭✭✭✭✭

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), "")`

Does anyone have a solution for this?

Christina

Tags:

• ✭✭✭✭✭✭
Options

What exactly are you wanting the formula to accomplish?

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
edited 08/31/21
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!