Using Reference Sheet to toggle Checkbox
Hello,
I've been digging and digging through Smartsheet posts and other references and cannot seem to find a comparable reference to use for my use case.
I have 2 sheets.
The first sheet stores PMs for equipment inspections. There is a column for "Equipment Name" and a column "PM Date".
The second sheet has a column for the "Equipment Name" then a check box for each month of the year.
So what I'm trying to accomplish is reference these 2 fields to toggle a check box on the second sheet to match the Equipment Name to the same name from the first sheet and check the box for the month if a PM for that equipment matches.
So for instance, It looks through the PMs (of which there can be multiple months for the same lift) and checks the box if a PM matches the month.
So in the attached screenshot,
Sheet 2
Sheet 1
You can see 2 dates for SCC 1, Jan and Mar.
Is there any way to utilize the PM date Month/Year to check the box for that month?
I'm still digging for a way but honestly I'm a bit lost. I'd greatly appreciate any guidance.
Best Answer

Hey Jerald I think you are pretty close to this, however INDEX/MATCH will only find the first match, not all matches, so you'll want to use something else.
Also, INDEX doesn't have logic as the first argument (ie you can't put INDEX({SCC Raymond Month}=1, … ) because it doesn't work with that.
Try this formula in the January column of Sheet 2. Make it a column formula. For the other columns, simply change the "1" to "2", "3", "4" etc to get the month you want:
= IF(COUNTIFS( {ABW Unit Number}, [ABW Unit Number / Serial]@row, {PM Date}, MONTH (@cell)="1") >0, true)
{ABW Unit Number} is a crosssheet reference to the entire ABW Unit Number / Serial column on Sheet 1
{PM Date} is a crosssheet reference to the entire PM Date column on Sheet 1
Edit: I just saw you might also need to check for the year. If so then you can add any number of criteria to that formula above. Here's the formula with a check for current year added:
= IF(COUNTIFS( {ABW Unit Number}, [ABW Unit Number / Serial]@row, {PM Date}, MONTH (@cell)="1", {PM Date}, YEAR(@cell)=YEAR(TODAY())) >0, true)
Answers

It also does not have to be a check box, but input a letter or something like X or Y in the month box.

So I've changed my process a little and tried to simplify the task.
I created a helper column on the first sheet that takes the date and converts it to just the month.
So now I'm trying to write a formula that states, if value is 1, check box. But also match the name. Here's my best guess so far….
=IF(INDEX({SCC Raymond Month} = 1, MATCH([ABW Unit Number / Serial]@row, {SCC Lifts}, 0)), 1, 0)
But it is not working. Still trying things. 
Hey Jerald I think you are pretty close to this, however INDEX/MATCH will only find the first match, not all matches, so you'll want to use something else.
Also, INDEX doesn't have logic as the first argument (ie you can't put INDEX({SCC Raymond Month}=1, … ) because it doesn't work with that.
Try this formula in the January column of Sheet 2. Make it a column formula. For the other columns, simply change the "1" to "2", "3", "4" etc to get the month you want:
= IF(COUNTIFS( {ABW Unit Number}, [ABW Unit Number / Serial]@row, {PM Date}, MONTH (@cell)="1") >0, true)
{ABW Unit Number} is a crosssheet reference to the entire ABW Unit Number / Serial column on Sheet 1
{PM Date} is a crosssheet reference to the entire PM Date column on Sheet 1
Edit: I just saw you might also need to check for the year. If so then you can add any number of criteria to that formula above. Here's the formula with a check for current year added:
= IF(COUNTIFS( {ABW Unit Number}, [ABW Unit Number / Serial]@row, {PM Date}, MONTH (@cell)="1", {PM Date}, YEAR(@cell)=YEAR(TODAY())) >0, true)

Thank you so much for your reply.
I finally figured out something that works perfect literally 40 minutes ago haha.First I had to create a Helper column that converted the PM date to just a month. Column Month.
Then on my Overview page:
I created this formula:
=IFERROR(IF(INDEX(COLLECT({SCC Month}, {SCC Lifts}, Lift@row, {SCC Month}, ##), 1) = ##, 1, 0), "")
SCC Month = The helper column from the first sheet.SCC Lifts = The Lift/Serial names.
## = The Numerical value for each month.
Your formula I feel is super close to doing the same thing as mine.
But hopefully both your solution and mine will help someone in the future!
Going to mark your answer as the answer cause choosing my own feels facetious. 
Cool, glad you were able to find a working option. You're right that they are similar approaches, my formula just basically collapses the MONTH and YEAR into 1 formula instead of separate helper columns, but both work great!
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives