Looking up values, and then enabling to choose from another list

Hello everyone,

well I got a problem and after looking it up didn't find the solution online, so maybe someone can shed some light on it.

Okay, so imagine a list of names (for example: John, Pete, Michael, Anthony). First I made a data validation cell (list type) so I get a cell with the drop-down selection of the values (names in the list). So that cell can have values of John, Pete, Michael or Anthony - whichever one I select from the drop-down menu.

Then I make additional list cells for each of the names, with different values for each of them.

For John: Football, Basketball, Sneakers
For Pete: Football, hockey stick, Skates, bow
For Michael: Volleyball, Diving gear, running shoes
For Anthony: Sweatpants, Tennis ball, Ping pong paddle

So here we get to what I need, and that is a function/macro for excel to check the name in a cell which contains the drop down list with names, and returns the value of the cell to be the drop-down list for that specific name.

To explain further, I have a cell with the drop-down list of names (John, Pete, Michael, Anthony) in which I select one of them. Then the funcion/macro in another cell makes that cell become the drop-down list cell for that specific name, in which I can again select one of the values.

For example, I select Pete in the first cell, and the second cell becomes the one with drop-menu options: Football, hockey stick, skates, bow.

I made an excel file for someone who knows how to solve this to type in the equation/macro, and attached it, named "Excel help for forum".

Thanks

Sorry,

attached is the sample workbook - so the cell next to the "NUMERICAL VALUE" where the question marks are should display a numerical value depending on what the "gear" selection is,
and for a specific name (John football is 15, while Pete football is 13).

Also, so that it is a number, which can be added, subtracted, etc. without any further commands.

Is that possible?

Yeah, tried them all out and they are OK.

Well tried out your formula, and it worked...

Thanks again, everyone!