Formula for checkbox if meets criteria
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
Answers
-
What exactly are you wanting the formula to accomplish?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!