Excel help - Drop down boxes, validation, etc

[jefffff34]

Program being used - Excel Mac 2011

Basically I have a lot of spreadsheets that I use for gambling (I'm not a big time gambler, I just want to be informed when I do punt). My most detailed one is for AFL for a number of different categories such as most goals, first goal, most possession, dream team points, etc, etc.

Anyway, in previous years I have all of my data of each team in a separate tab, which I still have....

Eg. Eddie Betts....1 2 1 3 1 0 4 or whatever his goals tally is, added up at the end of it with the formula I have worked out. Easy.

Then I have had to copy all of the players that are playing in the game, with their finalised data, into a separate tab, which then resulted in me doing a whole lot of formulas in that tab, etc, etc...very time consuming and the long way around.

What I am wanting to do is on the new separate tab is the following headings...

Club - Player - Data

Club would have the following choices...Adelaide, Brisbane, Carlton, etc, etc

This would then be linked with the players on each tab... In my example page I've made, I've been using Carlton and Richmond as they are the first game of the new season. So for example it looks like so...

Club - Player - Data
Carl - Tom Bell -
Carl - Levi Casboult

etc, etc

What I need to know how to do is how to get the club name, linked with all of the players in their separate tabs (the players names are A3:A30 in each of their tabs btw) so that if I chose Richmond, the option would be Cotchin, Deledio, etc, and then if I chose Tom Bell first for Carlton, it would then come up with his data cell (all data cells are DF3F30 in their tabs).

Thus meaning, I chose the club by a drop down box, I then choose which players I need, and their data pops up instantly.

As an example in the Carl tab, "Tom Bell" is A3, and his data is DF3..."Levi Casboult" is A4 and DF4, etc, etc.

(I was going to use my photobucket login to demonstrate better, but it appears it's been temporarily closed for not being used for ages!)

Thanks!

 

[funk44]

Are you able to upload a snippet of the data somewhere so i can take a look?

If i understand you correctly the simplest solution would be to write it in VBA

Edit: On second thought if you just want to view the data you are likely creating your problem by running a tab for each team. If there is no specific reason why you need to have the data in separate tabs i would suggest you put in all in one sheet then run a pivot table and filter (using slicers) by team then player. That should solve your problem

 

[jefffff34]

Are you able to upload a snippet of the data somewhere so i can take a look?

If i understand you correctly the simplest solution would be to write it in VBA

Edit: On second thought if you just want to view the data you are likely creating your problem by running a tab for each team. If there is no specific reason why you need to have the data in separate tabs i would suggest you put in all in one sheet then run a pivot table and filter (using slicers) by team then player. That should solve your problem

My tabs.

Tab data example.

Selecting the team (It's an example...these aren't actually linked to anything yet.)

And then what I would like to do...select the player, which would then automate the data that is in the image two pics up.

Hope that makes sense.

 

[funk44]

Still not exactly sure what you are after. Have you considered my suggestion of running all your data from a single sheet then viewing your data via a pivot table?

 

[jefffff34]

Still not exactly sure what you are after. Have you considered my suggestion of running all your data from a single sheet then viewing your data via a pivot table?

Yeah I tried that...it's doable for some of what I want but not others...

 

[funk44]

Sorry mate, but unless you are able to clearly articulate what you want to achieve and make a portion of your data available to assess where you are having problems, i am not going to be able to help