Using Reference Sheet to toggle Checkbox

Jerald_B

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.

Brian_Richardson

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 cross-sheet reference to the entire ABW Unit Number / Serial column on Sheet 1

{PM Date} is a cross-sheet 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)

Jerald_B

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

Jerald_B

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.

Brian_Richardson

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 cross-sheet reference to the entire ABW Unit Number / Serial column on Sheet 1

{PM Date} is a cross-sheet 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)

Jerald_B

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.

Brian_Richardson

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!