Range dependent on cell value

[enigmaes]

I'm missing something basic, but haven't used Excel for years. I'm weighing dependent dropdowns versus a nested "IF" statement to define what's eligible to be selected for column B range. Is there a better way?

I have a pull down option on Sheet 1 Column A.
Apples
Bananas
Oranges

On Sheet 2, I have columns of data with ranges specified by the name, and a column with quantity. So, in Apples, the range would be the 4 rows in that column, and it would be named, creatively, Apples.
Apples Banana
Ambrosia 12 Banana 15
Braeburn 3 Plantain 24
Red Delicious 25

If I pull down Apples on sheet 1, I want to be able to pull down on Sheet 1, Column B to be able to select Ambrosia, Braeburn, or Red Delicious, and then have that populate, along with the quantity from sheet 2 in Sheet 1, Column C.

Thanks in advance for help!!

 

[enigmaes]

And I think I want to populate quantity using either vlookup or index/match.....

 

[enigmaes]

Got it! Used Nested "IF" statements for the data validation source, that changed to a named range, depending on what the value of the cell was in column A. I then used "VLOOKUP" to return column 2 data for a named range that overlapped. Thanks for reading!