Is it possible to add drop down list using data from multiple sheets in Google sheet?

Question

I'm working on inventory management using Google spreadsheet. There are 5 sheets within the same spreadsheet. I want to create a drop down list in sheet 5 using data from sheets 1-4. I manage to create the drop down list if I use data from one sheet only. How can I create the drop down list which include all the data from multiple sheets? Do I need to use Google Apps script?

The link to first image (shows all sheets): https://drive.google.com/file/d/1ENWeKI9Lcmg4ynz4LV6mnvP0opdMSArf/view?usp=sharing

The link to second image (shows sheet which I will add drop down list): https://drive.google.com/file/d/1NgBDqHH0f7z5KA07wSVAylJ4WcMnAvPR/view?usp=sharing

Answer 1

You may create a drop-down list after you join the columns from different worksheet into single column then refer to the column as list, here is formula to join multiple columns into single column:

={QUERY(C:C,"Select C where C is not null");QUERY(B:B,"Select B where B is not null")}

The original worksheet has two columns with different values that you want to combine it into a single column only, after it is done then you can add Column D as drop down lists for values from both Col B & Col C.