Formula to make it to check the checkbox in another sheet.

Marie_Louise22

Hi all

Hope you can help me.

i'm trying to make a formular that can help me check off the checkbox in another sheet.

So its going to be somekind of formular that looks into another cell and if it is checked off it is going to checkoff in that sheet as well.

So 1. sheet is the Intake sheet. - The 2. sheet is the tasklist.

The orders come though to the intake sheet and then they are copied (automatic copy) into a tasklist. When the task is finished the status will be changed to done and it auto checkoff the "Done" checkbox column. So instead of manually going into the intake sheet and checking this one off too, I want it to recognize when the tasklist is checked off so will the task in the intake sheet.

right now i have been working with this:

=IFERROR(INDEX(COLLECT({Tasklist DK Range 3};{Tasklist DK Range 5};[project number]@row);1),"")

• It doesn't work it comes back with #unparesable

Hope you can see the solution!

Best!

L_123

The easiest way is to have an identifying number (key) that has a unique number for every submission. Then you can compare against that number to find unique submissions. The easiest formula for doing so is a countif as such:

=if(countif({submissionnumber},uniquenumber@row)>0,1,0)

You can further this by changing it to a countifs to check if the complete column in the other sheet is checked as well.

Marie_Louise22

@L_123

it doesn't work im afried..

Im not sure why, but im trying to let the intake-sheet know if the line (task) in the tasklist is checked done, so it automatically can check the checkbox done in the intake-sheet also..

hope you can see the solution!

thanks for trying! :-)

Jeff Reisman

@L_123<\/a> <\/p>

it doesn't work im afried.. <\/p>

Im not sure why, but im trying to let the intake-sheet know if the line (task) in the tasklist is checked done, so it automatically can check the checkbox done in the intake-sheet also.. <\/p>

hope you can see the solution! <\/p>

thanks for trying! :-)<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"L_123\",\"userID\":16866}}},{\"insert\":\" \\nit doesn't work im afried.. \\nIm not sure why, but im trying to let the intake-sheet know if the line (task) in the tasklist is checked done, so it automatically can check the checkbox done in the intake-sheet also.. \\nhope you can see the solution! \\nthanks for trying! :-)\\n \\n\"}]","format":"rich","dateInserted":"2022-09-07T13:31:18+00:00","insertUser":{"userID":148517,"name":"Marie_Louise22","url":"https:\/\/community.smartsheet.com\/profile\/Marie_Louise22","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-04-05T13:18:17+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/342602#Comment_342602","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/342602#Comment_342602

Is there an order number or something that is unique to the row in the intake sheet, which you can match against the task list sheet? That's what you need to make this work. Would the Project Number column be able to match the two rows across the sheets? Then you could use INDEX/MATCH:

=INDEX({Intake sheet checkbox column range}, MATCH([Project Number]@row, {Intake sheet project number column range}, 0))

Marie_Louise22

Hi@Jeff Reisman

Thanks for trying.. But it still gives me an #Unparseable error..

Billy from smartsheet gave me this formula;

=IFERROR(INDEX(COLLECT(What you want returned, What is the column matching the Project Number, [Project Number]@row), 1), "")

But it also dosn't work.. Can u see if there is anything missing? :)

PS, Yes we have a unique project no. that brings the tasks together :)

Genevieve P.

Hi@Marie_Louise22

It looks like you may have different language settings from the members providing answers.

For each of their responses, make sure that you're swapping out their commas for your semi-colons. For example, Jeff's response:

=INDEX({Intake sheet checkbox column range}, MATCH([Project Number]@row, {Intake sheet project number column range}, 0))

should turn into:

=INDEX({Intake sheet checkbox column range};MATCH([Project Number]@row; {Intake sheet project number column range};0))

Once you've tried the formulas again with the correct syntax for your language settings, if it still doesn't work, it would be useful to see screen captures showing how you wrote the formula in the cell (but please block out sensitive data).

Cheers,

Genevieve

Andrée Starå

Hi@Marie_Louise22

I hope you're well and safe!

Can you maybe share some screenshots?(Delete/replace any confidential/sensitive information before sharing) That would make it easier to 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 bymarking 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!