how make rowsources for multiple combobox from multiple sheets in the same colums

[maghari]

hi
I hope to find answering for my question , I would make rowsourses in combobox 1,2,3 from sh1,sh2,sh3,sh4 in the same columns b ,C,D without any duplicated , my idea depends searching from all sheets by the comboboxes because may some brands are existed in specific sheet , but not another for instance if the same brand is existed in sh1,sh2 then should not repeat in combobox 1 the same thing with rests combobox2,3 and sh3,4
note : I have a problem when I choose the batch from combobox1 should populate in combobox2, 3 but it only poulate in 2 this happens when I choose the the first and the second it from combobox1 , but if I choose another then every thing is ok I no know the reason from repeat the same item ,how can I fix it,please ?
if any body help I truly appreciate

[p45cal]

Test the attached

[maghari]

hi p45cl it shows me error when i run the form
in this line

Sheets("Append1").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False

[maghari]

you seem to hidden sheets "append1" I showed it but it gives me a new error in this line

Me.ComboBox1.List = [UNIQUE(INDEX(OFFSET(Append1!L2#,0,0,ROWS(Append1!L2#)+1),0,1))]
could not set this property invalid property array index

[p45cal]

hi p45cl it shows me error when i run the form
in this line

Sheets("Append1").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False

Without you having made any changes at all? That is, does it run properly as I attached it?

What version of Excel do you have?

[maghari]

2016

[maghari]

I work on your file directly when I run userform shows the error

[p45cal]

2 problems perhaps:
1. So you don't have the UNIQUE and FILTER worksheet functions available to you? (You'll get #Name? in cell F2 of the Append1 sheet)
2. Select say range B3:D8 of the Append1 sheet and press delete on the keyboard, then if you right-click in the same table and choose Refresh, does the data come back?

[maghari]

about point 1 I got error # Name from l2:n10 and point 2 I deleted the data doesn't come back after refresh

[p45cal]

about point 1 I got error # Name from l2:n10 and point 2 I deleted the data doesn't come back after refresh
1.Sorry, not F2 but L2 - so you don't have those - need a rethink on the macros.
2.This surprises me because Excel 2016 comes with Power Query built-in. Could you visit https://www.wikihow.com/Activate-Power-Query-in-Excel-2016 and try from step 2 and tell me how far you can go from there?

[maghari]

actually I found out my excel doesn't contain power query if you have another way to do that is ok , in all of about I try entering power query from the internet then I will inform you what happens with me
thanks again

[p45cal]

try

[maghari]

thanks very much this is better