if Cell A = [one of the item on sheetX Col A], Cell B = [Dropdown of items in Row of sheetX Row A]

[wktay]

Hi everyone,

I am trying to make a formula on the "Order" sheetname

There is another sheet called "Fruits", with details for The fruits and sellers.

E.g. Apple can be sold by Alice or Bob

Pear can be sold by Carl, David or Eliz

Fruits	Merchants		
Apple	Alice	Bob	
Pear	Carl	David	Eliz
Orange	Alice		

On the "Order" sheet, if Cell A is Apple, the Cell B will show a Dropdown of Alice or Bob. 

Cell A I can use Data Validation to get Fruits value.

I was like doing something like below, but I'm lost at the behind part. How do I propagate the dropdown based on what Fruit was selected?

=if(A1=Fruits!A2:A91;B1=??)

Also should the formula be written on a separate Cell instead of on Cell B?

Much Appreciated

[#2 · Accepted solution]

@wktay 

See Create Dependent Drop-Down Lists 

[#3]

@Hans Vogelaar 

Thank you very much

I have to adjust the 2nd List and use the Data Validation + INDIRECT( )