Non Blanks

  • Hello Deborah,


    With your objective to copy from the Status tab ... to the Production tab ...


    Could you manually fill in ... a few lines in the Production tab ... in order to properly visualize your objective ... :wink:

  • The thing is i want the production tab to automatically pull accounts from the status tab without any blanks.


    So the final output in the quality tab wont have any blanks in it.


    the accounts it pulls from the status tab has a criteria of pulling accounts depending on the first table of the target tab.

  • The thing is i want the production tab to automatically pull accounts from the status tab without any blanks.


    So the final output in the quality tab wont have any blanks in it.


    the accounts it pulls from the status tab has a criteria of pulling accounts depending on the first table of the target tab.


    Understand your request ...


    But currently ... your Status worksheet does not have any BLANK rows ... so why trying to overcome an obstacle .. you are not facing ...???


    Am I missing a constraint you have ...???


    Cannot identify what is the relation with the Target tab ...???


    As I said : Could you manually fill in ... a few lines in the Production tab ... in order to properly visualize your objective ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • let me explain the full steps.


    the accounts will be place in the Daily Production tab, then from their it will pick % of the data depending on the target into the status tab.


    then from the status tab depending on the target it will display accounts in the production tab.


    then it would randomly put accounts into the Quality tab.


    but when the accounts are displayed from the status tab to the production tab it displays even the blank. So the quality tab also picks up the blanks.

  • Perhaps try changing formula I gave you for helper column S in Production sheet to:


    =IF(COUNTIF(P$2:P2,P2)<=VLOOKUP(P2,Sheet2!$E$2:$F$11,2,0),"X"&COUNTIF(S$1:S1,"X*")+1,COUNTIF(S$1:S1,"<>X*"))


    You can keep the other formula I gave you to get the 5%, and now use this formula to get the one's not in the 5%


    =IFERROR(INDEX(Production!D:D,MATCH(ROWS(A$2:A2),Production!$S:$S,0)),"")

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • That formula goes in the sheet you want to extract the information to.


    So, in Status T2 of the workbook you posted earlier in this current thread enter formula (I replaced SAMPLE user in column Q with actual users from your Target Sheet):


    =IFERROR(IF(COUNTIF(Q$2:Q2,Q2)<=VLOOKUP(Q2,Target!$F$1:$G$12,2,0),"X"&COUNTIF(T$1:T1,"X*")+1,COUNTIF(T$1:T1,"<>X*")),"")


    copied down


    In the Production Sheet, A2 enter this formula:


    =IFERROR(INDEX(Status!A:A,MATCH(ROWS(A$2:A2),Status!$T:$T,0)),"")


    copied down.


    Your existing VLOOKUPS will pick up the rest of the relevant info

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!