Checking a box based on data from a cell in a different sheet

Tanya Boehmke

Hello,

I have two sheets. On one I have a column noting the status of a contract, in drop-down style. I would like for when the status is complete on this sheet, for it to check a box on another sheet. Is this possible?

Paul Newcome

@Tanya Boehmke

Following the appropriate steps to create cross sheet references, you should be able to make it work with a formula such as...

=IF(INDEX({Master Sheet Mkt. Status Column}, MATCH([Vendor ID]@row, {Master Sheet Vendor ID Column}, 0)) = "Complete", 1)

The above is assuming that the Vendor ID is unique to that row. If it is not and additional data needs to be accounted for to ensure that only the data from the single specified row is pulled, you would replace the INDEX/MATCH with a JOIN/COLLECT.

Andrée Starå

Hi Tanya,

Yes, it’s possible with a cross-sheet formula.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Tanya Boehmke

Andree,

In the Mkt Status column on this one, when the drop down "Complete" is selected.....

I would like it to check the box on this one in the Signed for 2020 Column

Thanks for your help!

ron.judenberg112096

make the Signed for 2020 a function using a cross-sheet reference to the Mkt Status field

Tanya Boehmke

Yes this is what I am trying to do, however I'm not sure how to write it as it also needs to find the correct vendor by vendor # or name. I know VLOOKUP and IF, but not together, or guessing there is an easier way?

Andrée Starå

@Tanya Boehmke

Did you get it working?

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Tanya Boehmke

Hi Andree,

The files are too big to delete sensitive info and share, will the screen shots above work?

Tanya Boehmke

Andree,

I figured this out and shared copies of both to you. When the Mkt. Status on the rebate tracker is marked complete, I'd like it to check the box on the Marketing Suppliers sheet.

Paul Newcome

@Tanya Boehmke

Following the appropriate steps to create cross sheet references, you should be able to make it work with a formula such as...

=IF(INDEX({Master Sheet Mkt. Status Column}, MATCH([Vendor ID]@row, {Master Sheet Vendor ID Column}, 0)) = "Complete", 1)

The above is assuming that the Vendor ID is unique to that row. If it is not and additional data needs to be accounted for to ensure that only the data from the single specified row is pulled, you would replace the INDEX/MATCH with a JOIN/COLLECT.

Tanya Boehmke

That's what I needed Paul, thank you! Again.......

Paul Newcome

Happy to help! 👍️

Andrée Starå

@Tanya Boehmke

Glad you got it working and thanks for sharing!

HannahLoc

Hi @Paul Newcome & @Andrée Starå ,

I have a similar question to Tanya's.

One Sheet1, I have multiple tasks with a checkbox associated with each task. When all tasks are complete (and all checkbox's are checked), I want this to be reflected in Sheet2. Sheet2 has a Status column, and I want the status to be changed to Complete once all checkbox's are checked. The Status column is a dropdown with other values (Not Started, In Progress, Complete)

Thank you for any help/advice!

Paul Newcome

@HannahLoc You are going to want something along these lines...

=IF(COUNTIFS({Source Sheet Checkbox Column}, @cell = 1) = 0, "Not Started", IF(COUNTIFS({Source Sheet Checkbox Column}, @cell <> 1) = 0, "Complete", "In Progress"))

HannahLoc

@Paul Newcome that worked! thank you so so much. never would've gotten there in the direction I was heading lol.

Paul Newcome

No worries and happy to help. 👍️