Create a list from selections made in a column without blanks.

  • Hi All


    Attached is a sample workbook which I hope explains my requirements.
    Basically, I am trying to create a list of selections made in a column, in another column but without the row spaces.


    Also, I need a way to remove the zeros from an indirect function as shown on the sample sheet.


    Any help is much appreciated



    Thank you

    Files

    • Sample.xlsm

      (19.58 kB, downloaded 114 times, last: )
  • Attached is your file with how I would get the result you desire.


    It does not use any formulas on the Setup sheet and works directly with the data on each "Material" sheet without needing to create a separate, temporary table.


    It uses Worksheet_Change event code in the Setup sheet Object module, so changes to Cell B4 will update the data in columns D and E


    The code is

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks for the response and although this is a great solution, it does not work for me in my intended worksheets.
    The sample sheet was as named "a Sample" and there are more columns and data to be included and extracted using the method I was originally implementing.


    Using your macro is not easy for me to understand or to edit.


    Is there a solution to my original post request?


    Thank you

  • To get rid of the 0 values in the Setup sheet change the formula in D4 to


    =IFERROR(IF($B$4="","",IF(IF($B$4>0,INDIRECT($B$4&"!$F"&ROW(A7),TRUE),0)=0,"",IF($B$4>0,INDIRECT($B$4&"!$F"&ROW(A7),TRUE),0))),"")


    and copy down.


    If you want a formula to populate the second table on each material sheet then somebody better than formulas than me will need to help you! I could do it with VBA but the code would be similar to the code I used in my first reply, so it would not be easy for you to modify to suit your actual needs.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi,


    In sheet SETUP


    In E4 use this formula:


    =IF($B$4="","",INDIRECT($B$4&"!G"&ROW()+3)) and drag down


    and in D4 use this formula:


    =IF($E4="","",IF(LEN($B$4),INDIRECT($B$4&"!"&"F"&ROW()+3),"")) and drag down



    In sheet Material1, Material2, Material3


    In F7, then drag to G7 then drag down, this ARRAY FORMULA:


    =IFERROR(INDEX(B$7:B$26,SMALL(IF($D$7:$D$26="y",ROW(B$7:B$26)-ROW(B$7)+1),ROWS($F$6:F6))),"")

  • Thank guys, I used KjBox's suggestion to remove the "zeros" and Ingo_Ingo's suggestion with the array formula to create a new list of selections made.


    KjBox, I did appreciate your first response and please don't think I am not grateful, but we are not all as clever as you pros's when it comes to VBA and Array formulas, I just wanted something easier to modify and the array formula was just the ticket.


    Great work as always, keep it up


    Thank you