Linking checkboxes across 2 sheets

Chris Farley

I've got 2 spreadsheets for tracking job progress. One is an end of day report filled out by field personnel. The other is a collection of all the information about various jobs gathered from the end of day reports and manually entered data.

I want to link a checkbox when any end of day report has a "Install complete" checked and it matches the Project name in my job folder sheet. There are multiple entries with the same project name in the end of day report and I only need the "Install Complete" checked once to return a true value for the job folder sheet.  

 

In the attached screen shots I'm trying to link the highlighted checkbox when the highlighted project matches. 

 

How can I do this? 

Marcin P

Hi,

You need to keep column set up as a check box and put there VLOOKUP formula, which search your project name within source sheet.

Paul Newcome

Do you have more than one end of day sheet submitted by field personnel, or do they all fill out the same sheet?

 

If they are all filling out the same sheet, then there are a few different ways to accomplish this, all of which are fairly straightforward.

 

If you have multiple sheets you are looking across and need only one of them to have the box checked, it could get a little more complex.

Chris Farley

They fill out a form that fills a single sheet. It is multiple rows/entries for the same project. 

Paul Newcome

Ok. Try something like this...

 

=IF(COUNTIFS({Sheet 1 Range 1}, @cell = [Project Name]@row, {Sheet 1 Range 2}, @cell = 1) > 0, 1)

 

Sheet 1 Range 1 is the Project Name column on the sheet being populated by the form. 

 

Sheet 1 Range 2 is the Install Complete column on the sheet being populated by the form.

 

This will count how many times the box is checked on the sheet populated by the form for that particular project. If there is at least one (more than zero in the formula), it will check the box.

Chris Farley

=IF(COUNTIFS({End of Day Report Range 14}, =[Project Name]1, {End of Day Report Range 13}, =1) > 0, 1, 0)

 

That got it to work. Thanks. I've been beating my head against a wall for a while now. 

Paul Newcome

Happy to help. I had a very similar need a while back and did my own fair share of beating my head against a wall. Haha

mbsamuel6

@Paul Newcome

Hi there, I have a similar problem and don't seem to get your answer as it relates to what I'm trying to do.

I am trying to do the same as the original poster where a user checks a box in sheet 1. And that box is automatically checked in sheet 2.

To be more specific here is sheet1:

In Sheet 1 when column 16 (Order PC/Laptop) is checked for a specific "New Hire Name" I would like the column titled "Completed" in sheet 2 as seen below to be marked as well for the row "Order PC/Laptop" for that same "New Hire's Name".

So a better way of saying this in sheet 1 when the checkbox for Order PC/Laptop is checked for Nick Chubb, I would like the checkbox in the "Completed" column in sheet 2 to be checked for Nick Chubb.

Is this possible? Especially with the way how I have both sheets setup?

And if so is VLookup the best approach or would cell linking be better?

Paul Newcome

@mbsamuel6 Try something like this...

=INDEX({Sheet 1 Order PC Column}, MATCH(Info4, {Sheet 1 Name Column}, 0))

mbsamuel6

@Paul Newcome

That worked. Thank you!!!

One thing that helped is that I had to take the "restrict to checkbox" only option off. Thank you sooooo much!!!!

Paul Newcome

Happy to help. 👍️

Stacy Meadows

@Paul Newcome

You seem to know your stuff... I could really do with your help im a newbie to smartsheet and not much experience in formulas.....

I have sheet A and sheet B

When a checkbox is ticked on sheet A I want the information from several columns in that row (Name, Value, Account number, YTD) to automatically copy to Sheet B ....

Also would I need to do this in several columns in sheet B to bring the relevant columns over ?

Your help would be much appreciated in simple terms lol 😂

Andrée Starå

Hi @Stacy Meadows

Adding my answer here as well.

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Stacy Meadows What is the reason for bringing all of this information over to Sheet B?

MANNA

hello,

how do I link only 1 or 2 column to a different sheet when box is checked?

Andrée Starå

Hi @MANNA

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!