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)
