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

Jared Ross
Jared Ross ✭✭
edited 10/26/20 in Formulas and Functions

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.

Best Answer

Answers

  • 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)


  • 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?

  • 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?

  • 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).

  • 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?

  • 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!