Forum Discussion

Donna Hernandez's avatar
Donna Hernandez
Copper Contributor
Sep 22, 2017

Formula help please

Hi all, I am trying to create a few formula's for the following

# of New Boxes QAed:     >>>>0# of Those Boxes Failed QA:     >>>>0
Failed Box #'s:Part #:PG #:Reason For Failure:
    
    
    

 

What I want to do is get information from the bottom sheet to the top sheet. 

I want to say if a box has failed (which there will be an "X" in the box) I want the box #, part #, page # and reason to automatically copy to the sheet above.

I also want next to "# of new boxes qaed" to add up for how many done that day and in the "# of those boxes failed qa" to only add up the boxes that failed.

 TaskBoxPassed FailedPart #PG #Reason
1       
2       
3       
4       
5       
excel

7 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Sep 22, 2017

    Hi Donna,

     

    As usual, that could be done by several ways. If you attach small sample file not to to construct your data structure from scratch that will be easier to answer.

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Sep 22, 2017

        Hi Donna,

         

        The formula is in attached files, if you open them and save Production Sheet in some folder full path substitutes, thus works with closed file.

         

        Formula is well-known, i'll find the link some later with detailed explanation how it works. Please note that's array formula, that means you shall use Ctrl+Shift+Enter to enter it.

        For Box# it'll be

        =IFERROR(INDEX('[Production Sheet Template.xlsx]Sheet1'!$A$4:$H$33,SMALL(IF('[Production Sheet Template.xlsx]Sheet1'!$E$4:$E$33="X",ROW('[Production Sheet Template.xlsx]Sheet1'!$A$4:$A$33)-ROW('[Production Sheet Template.xlsx]Sheet1'!$A$4)+1),ROW(1:1)),3),"")

        put it in A8 and drag down. Latest number 3 in the formula is the column number in Production Sheet, for other fields you shall change only it.

         

        Important: array formulas don't work within merged cells, so i unmerged them in DPR. In general, better never to use merged cells, you have only issues with them.

         

         

Resources