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

  • 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

  • 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


    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:


  • 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

Participate now!

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