Search values and populate based on user input

[benzoni86]

Hi,
I have the attached file I use to create an output file to be loaded in a system.
the file has 3 tabs:
Formula: where in B:D, K:L, N:R there are the configurations to populate tab 'assortments' while F:I is used to populate tab 'Sites_assigned'
Assortments: in columns A:D user enters the input that will the be used to auto-populate columns E:K
Sites_Assignedopulated with the valid options found in tab 'Formula, columns F:I.

how the file works:after user enters input in tab assortment, which generates the assortment code in column E. this code is reported in tab 'Formula in column F(12 cells for each assortment code in the 'Formula' tab. then in columns G, H and I cells are populated based on the combinations found for Brand and Channel in columns B and C.
This array formula is pretty tricky to me, first of all because i have created a different one for every new populated row in tab 'assortments' meaning that, for the row 2 in the assortment tab i have created the array formula in cells G9:I20, for row 3 i have created the array formula in cells F219:I32, and so on..(i have currently set it for 39 rows, but would be ideal to have it unlimited
my aim is to have users populating the tab 'assortment' as they are currently doing, and a formula/macro, which is retrieving all the valid combinations per assortment based on its brand and channel and list them in the tab 'Sites_Assigned'.
hope the description is clear enough...anyone who can support me?

 

[RDAngelo]

Possibly something a bit simpler, using the AdvancedFilter method...

    Range("tbl").AdvancedFilter _
                    Action:=xlFilterCopy, _
                    CriteriaRange:=Sheet1.Range("G1:H2"), _
                    CopyToRange:=Sheet1.Range("G8:I8")

after creating the criteria range from the Assortments tab results

 

[benzoni86]

Hi,
not sure i understand how to use it...adding the code i get the message 'The extract range has a miising or invalid field name'
Also,would this eventually work if i had multiple rows populated in tab assortment, meaning therefore multiple inputs?

 

[RDAngelo]

I got the same error until I moved the data to a new, clean worksheet.

No, it would not. Some changes would be needed along with how and where you would want to display the results.