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.

Find more posts tagged with

Support

help

Accepted answers

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)

All comments

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.