Help with IF/Index/Match Statement
Good day, Struggling trying to get this to work.
I need to track if our sites have had an audit during a particular year.
Sheet 1 "Site Audit" and contains site audit information. Mainly the site name and the date it was audited is the only information I need. I created a helper column to extract just the year
Site | Date of Audit | Helper Column
ABC | 02/15/2020 | 2020
DEF | 007/25/2020 | 2020
Sheet 2 :Site Audit Tracker" has a list of all of the facilities with columns representing a given year. My "want" it to have a "check" to represent if the site was visited during that respective year.
Site | 2020 | 2021
ABC | 1 |
DEF | 1 |
I have been able to successfully pull the year in the 2020 column by using the Index/Match using the following formula.
=INDEX({Site:Site}, MATCH(Site@row, {Helper Column}, 0))
So now it looks like:
Site | 2020 | 2021
ABC | 2020 |
DEF | 2020 |
But stuck on how to make it a true/false (0,1). I figured there is a IF function in this process somewhere, but can't figure out how to successfully implement it. (I realize each respective Year column will need its own formula - Im ok with that)
Answers
-
Hey @Dan Walsh!
I believe this will help accomplish what you are trying to do
=IF(ISDATE(INDEX({Site:Site}, MATCH(Site@row, {Helper Column},0))), 1 , 0)
-
Unfortunately it didn't work.
I think it is because I need it to look specifically for 2021 in the helper column on Sheet 1. If I have another entry for the same site, but a different year, it doesn't compute.
So Layman's term for column 2020 or 2021 in Sheet 2
If the Site matches in Sheet 1 and 2, put a check mark in the column 2020 if the audit was completed in the year 2020
Likewise, if the audit was completed in the year 2021, then but a check mark in the column 2021 for the respective site.
And to clarify, Sheet 1 can have multiple entries for each site for example:
Site | Date of Audit | Helper Column
ABC | 02/15/2020 | 2020
DEF | 07/25/2020 | 2020
ABC | 08/25/2021 | 2021
Sorry if this is confusing, Its confusing to me as I type it. I appreciate the time you are taking to assist.
-
@Dan Walsh, there are two ways to do this, one will give you the count of how many times a site was visited and the second uses the same formula combined with an IF statement to make it a checkbox.
To get the count of how many times a site was visited use the following formula: =COUNTIFS({Helper Column}, 2020, {Site}, Site@row) . Change the year as needed
To convert into a checkbox use the following formula: =IF(COUNTIFS({Helper Column}, 2020, {Site}, Site@row)>0,1,0)
Site Audit Sheet:
Site Audit Tracker to find count
Site Audit Tracker with checkbox
-
You..my friend, are a genius!
That is exactly what I was looking for! Thank you very much.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!