Forum Discussion
Example - Mechanical Inspection using AQL
1) It'll be much harder to change the formula and much longer to develop it. If you don't like to have all these additional cells together with main data, such data preparation calculations could be moved on separate sheet (both table helper columns and intermediate calculations) and such sheet could be hided not to show it to end user.
2) Not to color cells with data we may check if the cell is empty or not by one more criteria
=(ROW()>ROW($C$2))*(ROW()<=($C$1+ROW($C$2)))*(C3="")3) Not sure I understood this question, could you please clarify a bit.
Thank you for the suggestion. I will most likely end up adding the formula to the sheet where the table is located as the sheet involving the recording of the feature will have to maintain a certain appearance that will need this hidden.
The new formula you updated for #2 was exactly what I was looking for. I was trying to ask you (in question 3) if adding the step about switching from yellow to white when data is entered would be difficult based on what you initially provided but I see now how you included it.
.
.
One question I have regarding ALL:
As I'm messing around with the file you sent I'm noticing when a value for AQL and Quantity are chosen that correlate to "ALL" in the TABLE - this results in all 20 piece #'s flagging yellow. How would I alter this so that "ALL" in the TABLE tab is reflective of the Quantity entered on TAB 1?
[for example] The attached filed I added, the Quantity is 5 and AQL is 0.01%. From the table this is defined as ALL and TAB 2 highlights all 20 piece #'s listed on TAB 2. In this case there are only 5 parts in the quantity so ideally only 5 piece #'s would need to be highlighted on TAB 2.
Essentially the connection I am making is if ALL is the chosen cell from the TABLE then the highlighted cells would be reflective of the exact quantity indicated on TAB 1.
This is where I mentioned in the original post that I was skeptical about ranges and considered making this table much larger to include one number per lot size in fear of this exact need being to complex.