Formula to return a checkbox on Sheet B, if a % column in Sheet A = 100 + move rows

A.A

Hello there,

I have 2 sheets:

Sheet A - Base File, where i have % Column where i input the current numbers.

Sheet B - Transfer to File, where i need to add a checkbox to a row, if the % Column in Sheet A is 100 %.

I have managed to make the reference where:

=IF({Base File Range 5} = 1, 1, 0).

BASE FILE

TRANSFER TO FILE

My question is, if on the Base File, i then move the Row (from row 3 to row 8), will the reference follow? Or do i need to do some sort of Vlookup, and what would that be?

Every item in the Task Name Column will be the same in either sheet, so this can be used as a unique identifier if required.

The plan is to build this out across multiple sheets, so just want to make sure i have it correct before i start committing to it.

Additionally, is it possible to make extend this in a quick way (drag the formula down) instead of doing it cell by cell.

Thanks,

Jgorsich

Your "Task Name" column has a space in it, so you have to put square brackets around it when using it in a formula (same if you have numbers in it, btw. No downside to ALWAYS using square brackets, they just take a bit longer to type when not needed).

Try this:

=if([Task Name]@row="","",if(index({%Complete},match(Task Name@row,{Task Name},0))=1,1,0))

Jgorsich

Index(Match()) is generally a better choice then Vlookup. If you want to get REALLY fancy you can do an Index(Collect()) which allows for multiple match conditions, but that doesn't seems to be necessary for you.

To get the % complete from one sheet to another, you can do just an index(match()) like this:

=index("your percent column in base file",match(taskname@row,"your task name column in base file",0))

( https://help.smartsheet.com/function/match , https://help.smartsheet.com/function/index , https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell )

To convert that to your checkbox, wrap it in an if statement like you did before:

=if(index("your percent column in base file",match(taskname@row,"your task name column in base file",0))=1,1,0)

To be able to make it into a column formula and not have any issues with errors, wrap it an error trap:

=iferror(if(index("your percent column in base file",match(taskname@row,"your task name column in base file",0))=1,1,0),"")

OR wrap it in a test if to prevent the error from being thrown by only executing the formula when there is a value in your taskname column:

=if(taskname@row="","",if(index("your percent column in base file",match(taskname@row,"your task name column in base file",0))=1,1,0))

Then, just right click on it and make it a column formula. Good luck!

A.A

https://community.smartsheet.com/discussion/comment/434235#Comment_434235

Thanks for your assistance.

When inserting the final formula, and then adjusting for my actual data, it comes up with an unpaseable error.

=if(taskname@row="","",if(index("your percent column in base file",match(taskname@row,"your task name column in base file",0))=1,1,0))

FORMULA UPDATED:
=if(Task Name@row="","",if(index({%Complete},match(Task Name@row,{Task Name},0))=1,1,0))

Any ideas where i am going wrong?

Jgorsich

Your "Task Name" column has a space in it, so you have to put square brackets around it when using it in a formula (same if you have numbers in it, btw. No downside to ALWAYS using square brackets, they just take a bit longer to type when not needed).

Try this:

=if([Task Name]@row="","",if(index({%Complete},match(Task Name@row,{Task Name},0))=1,1,0))