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

Copper Contributor

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?


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


Much Appreciated


2 Replies
best response confirmed by wktay (Copper Contributor)



Thank you very much


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

1 best response

Accepted Solutions