INDEX AND MATCH MULTIPLE WORKSHEETS

[TMAX]

Workbook 1 has a data base with several sheets of data.

Sheet 1 (Fruits)

Row A Item (apples, oranges)

Row B Cost ($)



Sheet 2 (Grains)

Row A Item (Nuts, Grains)

Row B Cost ($)

This book will remain closed.



Workbook 2

Row A (Drop down menu for Fruits or Grains)

Row B (Drop down menu for apples or oranges if Row A is Fruit)

(Drop down menu for Nuts or Grains if Row A is Nuts)

Row C Price

Before I had Workbook 1 with only one worksheet with apples, oranges, nuts, grains all together.

This is the formula that I used and it worked well except my data sheet grew to large.

=IF(B8="","",INDEX('[Inventory Sheet.xls]Vitamin Mineral'!$T$5:$T$700,MATCH(B8, '[Inventory Sheet.xls]Vitamin Mineral'!$E$5:$E$700,0)))

I wanted to break it up into several sheets in Workbook 1 but I am having trouble selecting worksheets.

 

[TMAX]

My question is can you make an index and match function to select specific worksheets in a closed workbook based on the data from A1(drop down menu) and B1 (drop down menu). I am not sure if I am explaining myself correctly or if this has been covered before. Thank you for any help.