Multiple Check Boxes in Cross-Referenced IF Statement

JBLS_

I have a master spreadsheet that details the progress of a project. I'm in the process of rolling all the information on that sheet into another that consolidates anywhere from 1-100 lines (check boxes) into a single line. The example below shows an example of 3 check boxes.

That being said, I'm creating a formula for a checkbox so that it will find any number of check boxes on the master sheet and, if they are all checked, check itself. I can sort the master sheet by the parameters I'm looking for and then individually reference each cell using an IF/AND formula, as seen below, but this is super time consuming. I figured out a SUMIFS workaround but I feel like there has got to be a simple alternative with cell linking, INDEX/MATCH/COLLECT, VLOOKUP, or another formula.  

=IF(AND({CABLE SCHEDULE Range 3}=1, {CABLE SCHEDULE Range 4}=1, {CABLE SCHEDULE Range 5}=1),1)

Thank you!

Paul Newcome

Are you able to provide some screenshots with confidential/sensitive data deleted or replaced with "dummy data"?

 

There is the possibility of numerous solutions, but that all depends on the details.

JBLS_

Here is a copy of my current roll-up sheet and formulas. I'm in project management for electrical construction and tracking a medium-sized project.The above question is in reference to the blue outlined mark-up. The red are how I'm currently rolling the rest of the data, using a workaround I figured out after I posted this question which displays the current progress as well, before checking itself at completion. 

I'm relatively new to Smartsheet, but this roll-up feels very clumsy. I don't know if it will grow well as the project does, with the limits there are on incoming formulas and data. Not to mention how it already has slowed down the speed on the main tracking sheet. ANY help at all would be really appreciated with streamlining those formulas with @cell/@row, VLOOKUP, or other formulas that I don't quite understand yet. 

Thanks again for your help!

Paul Newcome

Ok. So let me break it down the way I understand it thus far.

1. You have another sheet where there are multiple rows that can have the same EQ Tag.

2. You want to look on this other sheet for checkboxes in the QA/QC column where the EQ Tag is the same as the one on your rollup sheet.

2. If all of the rows containing that EQ Tag have the QA/QC box checked, you want the QA/QC box checked for that EQ Tag on your rollup sheet.

.

Does this sound correct?

.

Once we get this part figured out, I'd be more than happy to try to help make the sheet more efficient. Unfortunately though it looks like you have A LOT of cross sheet references. Depending on how those references and ranges are set up, there may not be a whole lot of streamlining we can do. At the very least we will work in the @row and @cell references where we can and look at a few other things.

.

But first... Let's get this QA/QC box figured out.

JBLS_

Yes, that's exactly what I'm trying to do. 

Thank you!

Paul Newcome

Ok. So what we will do is first use a COUNTIFS to figure out how many times that EQ Tag shows up on the other sheet. That will tell us how many checkboxes we should have. Then we use another COUNTIFS to see how many checkboxes we actually have. We will make the comparison and tell the formula to check the box if those two numbers match.

.

To get how many times the EQ Tag shows up:

=COUNTIFS({EQ Tag}, [EQ Tag]@row)

.

To count how many of those boxes are checked:

=COUNTIFS({EQ Tag}, [EQ Tag]@row, {QA/QC column}, 1)

.

If statement to compare the two and give a check if they match:

=IF(first formula = second formula, 1)

=IF(COUNTIFS({EQ Tag}, [EQ Tag]@row) = COUNTIFS({EQ Tag}, [EQ Tag]@row, {QA/QC column}, 1), 1)

.

And there you have it.

JBLS_

That worked perfectly! Thanks so much!

If I wanted to provide the same visibility into the progress of the EQ (i.e. 1/2, 10/13, etc.) before it is checked at 100% complete - is there a way to streamline the process I already have in place for doing that with the conduit/wire/terms?

 

Paul Newcome

Are you referring to the two columns where the data is displayed as "# / #"?

JBLS_

Yes sir. 

Those are checkbox cells that display the data in a fractional format until 100% when it checks. 

Paul Newcome

One thing I see right off to help improve efficiency would be the use of @row. If your formula is referencing a cell on the same row, just replace the row number with @row.

 

[Column Name]1

turns to

[Column Name]@row

.

The % complete columns are also referencing data from the other sheet that is already present on this sheet.

You can simply use cell references on the same sheet to remove the repeated formulas and cross sheet references.

 

=IFERROR(ROUND([CND Data1]@row / [CND Data2]@row, 2) * 100 + "%", "N/A")

 

will return the same exact result as your long SUMIFS / SUMIFS and cuts out 5 cross sheet references. Multiply that change by the number of rows you have in your sheet, and that should make a difference in speed in an of itself.

 

2 Columns. 37 Rows visible. That alone is 74 cells affected. That would be 148 formulas and 370 cross sheet references removed from the sheet. And that's just the 37 rows that are visible in your screenshot. Add to that the efficiency improvements from replacing row numbers with @row in all formulas across the sheet, and you should already be seeing at least a slight difference in speed.

Paul Newcome

You can also remove the "3" columns from your sheet that are housing the JOIN function.

 

Simply find where you are referencing that cell, and insert the JOIN function directly into that formula.

 

Conduit1:

=IFERROR(IF(VALUE(.........................., [CND Data3]1, 1)

and

[CND Data3]1:

=JOIN([CND Data1]1:[CND Data2}1, " / ")

 

can be combined to say:

=IFERROR(IF(VALUE(.........................., JOIN([CND Data1]1:[CND Data2}1, " / "), 1)

.

There goes 3 columns of x amount of rows that the sheet doesn't have to worry about.

.

You can also remove your VALUE functions. The COUNTIFS are already returning numerical values, so the VALUE function is unnecessary and does nothing but drain just a little bit of processing efficiency from the sheet.

 

=IFERROR(IF(VALUE([CND Data1]1 / [CND Data2]1) = 1, ................)

can be reduced to

=IFERROR(IF([CND Data1]1 / [CND Data2]1 = 1, ................)

and don't forget @row

=IFERROR(IF([CND Data1]@row/ [CND Data2]@row= 1, ................)

.

I am also wondering if some of this data can be calculated on the other sheet and then use a simple INDEX/MATCH to pull it over. That will help reduce the number of formulas and cross sheet references on this sheet as well.

.

P.S.

That JOIN function can also be replaced with a slightly more simple formula.

=JOIN([CND Data1]@row:[CND Data2]@row, " / ")

will give you the same thing as 

 

=[CND Data1]@row+ " / " + [CND Data2]@row

 

I'm honestly not sure how much of a difference that would make (if any), but I figure it's one less actual function for the sheet to have to run.

.

I'm getting out of here for the weekend, but I'll be back on Wednesday (that's right... I get a 4 day weekend ​​​​​​​) to see how things are going and whether or not we can improve efficiency elsewhere.