Combine and show two list in data validation with if condition

**arunvijilan** · 03-02-2020, 03:30 AM

HI,
1. I have a list of "products", "raw materials", "packing materials" each in different sheets
2. and in another i have data validation to show this list with respective to the value in near by cell as "SALE", "PRODUCTS","MANUFACTURING", "PURCHASE"

Now,

1. if the cell has value "SALE" or "PRODUCTS" I want to get the list of "PRODUCTS"
2. If the cell has value "Purchase" or "Manufacturing" i want to show both list "RAW MATERIAL" AND "PACKING MATERIAL"

I found a VBA code mentioned below to do this ,

Please Login or Register  to view this content.

Now..how do i write formula in data validation for this to work.

**Pepe Le Mokko** · 03-02-2020, 03:48 AM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

**Pepe Le Mokko** · 03-02-2020, 03:49 AM

Please note that writing in uppercase is considered shouting. As we are all friends here, please refrain from doing that. Thank you

**davsth** · 03-02-2020, 04:29 AM

Lots of links search for excel dependent validation list
https://www.excel-easy.com/examples/...own-lists.html
https://trumpexcel.com/dependent-dro...list-in-excel/
https://www.contextures.com/xlDataVal02.html

if you wish to combine 2 lists the easiest is to jut paste the as a continuous range
Otherwise they can be linked by formulas to look up the ranges
or vba to create a joined range

building on the links
a combined list for packing materials and raw material
=IFERROR(IF(ROW(A1)<=COUNTA(Packing),INDEX(packing,ROW(A1)),INDEX(Raw,ROW(A1)-COUNTA(packing))),"")

If you have defined the ranges as packing and raw respectively

**arunvijilan** · 03-02-2020, 05:16 AM

Thanks for your reply,
Please note, my problem in little complex than dependent validation list

as per examples is the links provided

if it is fruits = fruit list will appear
if it is vegetable = vegetable list will appear.

But if i want both the list of fruits and list of vegetable appear as single list.

then, how do i do it ?

**davsth** · 03-02-2020, 05:33 AM

see my formula at the bottom
you create a physical range in the spreadsheet that combines both ranges, name it say both, and then link to it as per the articles
=IFERROR(IF(ROW(A1)<=COUNTA(Packing),INDEX(packing,ROW(A1)),INDEX(Raw,ROW(A1)-COUNTA(packing))),"")
counta(packing) is the number of items in packing so it pulls all of the items in packing into a column, then when it has run out takes the items from raw

**davsth** · 03-02-2020, 05:44 AM

Something like the below

I am sure you can adapt, if you had given an example, people could have looked at you specific problem

The green drop down chooses the category

the ranges on the other sheet, populate the yellow dropdown, with fruitveg being a combination of both of them