checkbox to see if there is a value in a cell on another sheet.

Jared Ross

I have a master progress sheet for a project with a bunch of different projects. To track my own progress on another part of the project, I've created a PRODUCTION sheet, as parts of my task don't need to be included in the master sheet. one of the columns of the MASTER sheet is production coordinator that can have several different employees names in it. What I would like to do is have a checkbox column on my PRODUCTION sheet that automatically populates to see if anyone has been assigned in that production coordinator column in the MASTER sheet.

IE - MASTER sheet has rows for projects X1 - X47. Someone was assigned as production coordinator for X11 and X20. I want my PRODUCTION sheet to automatically populate the checkbox with a check in a "Downloaded" column when the production coordinators have been populated in that master sheet so I know it's has someone on it.

Genevieve P.

Hi @Jared Ross

If I'm understanding you correctly, you just need to know if the Production Coordinator cell for a specific project has someone assigned (or, is not blank). As soon as there is any data in that cell, you want your Master Sheet to return a checked box. Is that correct?

If so, you could use an INDEX(MATCH to gather the content from the Production Coordinator cell that's associated with each project, and embed that in an IF statement to check the box IF the INDEX(MATCH is NOT blank.

Try something like this:

=IF(INDEX({Production Coordinator column}, MATCH([Project Name]@row, {Project Name in other Sheet})) <> "", 1, 0)

• {Production Coordinator column} - is the column in the Master sheet that either has a name or is blank.
• [Project Name]@row - is the cell in the column "Project Name" in your current, Production Sheet
• {Project Name in other Sheet} - is the column in the Master sheet that has the project name, to be matched with the current sheet's data in the Project Name column

Does that make sense? Let me know if this works for you! If it doesn't it would be helpful to see a screen capture of the Production Sheet with an explanation of what you want returned (but please block out any sensitive data).

Cheers,

Genevieve

Genevieve P.

Hi @Jared Ross

If I'm understanding you correctly, you just need to know if the Production Coordinator cell for a specific project has someone assigned (or, is not blank). As soon as there is any data in that cell, you want your Master Sheet to return a checked box. Is that correct?

If so, you could use an INDEX(MATCH to gather the content from the Production Coordinator cell that's associated with each project, and embed that in an IF statement to check the box IF the INDEX(MATCH is NOT blank.

Try something like this:

=IF(INDEX({Production Coordinator column}, MATCH([Project Name]@row, {Project Name in other Sheet})) <> "", 1, 0)

• {Production Coordinator column} - is the column in the Master sheet that either has a name or is blank.
• [Project Name]@row - is the cell in the column "Project Name" in your current, Production Sheet
• {Project Name in other Sheet} - is the column in the Master sheet that has the project name, to be matched with the current sheet's data in the Project Name column

Does that make sense? Let me know if this works for you! If it doesn't it would be helpful to see a screen capture of the Production Sheet with an explanation of what you want returned (but please block out any sensitive data).

Cheers,

Genevieve

Jared Ross

It's working, but completely inconsistently.

first image with my name in the green rows is the master. Other image is my production sheet. I'm using "TeamID" to reference the project. The first column of checkboxes I did manually to verify that a PC was assigned. As you can see, the formula only returned one correctly, and "#NO MATCH" comes up randomly. The formula I used is below.

=IF(INDEX({production coordinator master sheet}, MATCH(TeamID@row, {TeamID Master sheet})) <> "", 1, 0)

Genevieve P.

Hi @Jared Ross

How is the TeamID being populated in both sheets? Are you using a formula to create the ID or is it being manually input?

Jared Ross

ok... so I put it in manually, but it was correct. I tried it with a formula too in order to make sure things were correct, but was getting the same inconsistencies.

I've just realized that it's returning inconsistent results like that because the master sheet is having rows sorted different ways by different people in real-time.

Is there a way for the formula to reference another sheet consistently if the sheet being referenced has it's row sorting order being changed on a frequent basis?

Genevieve P.

Hi @Jared Ross

The order of the rows shouldn't matter as it's looking into the entire column. However, with the MATCH function there is an option to identify if the rows are in a certain order or not.

Try adding in a "0" in the MATCH function, like this:

=IF(INDEX({production coordinator master sheet}, MATCH(TeamID@row, {TeamID Master sheet}, 0)) <> "", 1, 0)

That identifies that the match type is not sorted (see here).

Jared Ross

Super interesting.... That fixed it. In a semi-unrelated question, will it still work with that 0 added in regardless of if/how the sheet being referenced is sorted? We have other sheets which have come up "broken" due to this same problem, where almost every cell is referencing another sheet.

IE - the formula works every time when the referenced sheet is sorted/unsorted every possible way?

Genevieve P.

Hi @Jared Ross

Sorry about the delay! Yes, the 0 just changes the default of how the MATCH is looking at that column. You can definitely try it in any of your other sheets where you have issues as well. If this hasn't resolved the result you're seeing, then there may be some other cause and we'd want to look into that one specific formula further.