Help. Drop Down Menu to return table of data

Help. Drop Down Menu to return table of data

Hi

I'm really stuck with this and not sure where to start and cant find a tutorial anywhere which may be because I am searching for the wrong keywords.

Basically I work in a school and want to do the following:

On Sheet 1 have a drop down menu of all the subjects we offer, in sheet 2 I have a table of data for each subject which looks like this (only bigger)

C B A
3 34 45 52
4 23 63 34
5 12 23 53

The C B A refer to achieved grades at the end of a course and the 3 4 5 refers to the level they started at when they joined the school. The numbers, e.g., 34 refer to the number of students who for example joined the school on a level 3 and went on to achieve a C grade when they left.

Basically what i want to do is when I select the subject from the drop down on Sheet 1 it will pull the relevant table of data from sheet 2 AND display it in the space on Sheet 1.

Can anyone please help! Totally stuck!

Thanks so much

Jeff

Hi Jeff,

Could you post a sample workbook showing the data layout and what you want it to look like after you select from the dropdown?

BSB.

Hi

Couldnt upload to here so here is the link to the file in my OneDrive, hope its clear enough.

https://onedrive.live.com/redir?resi...nt=file%2cxlsx

Thanks for replying

Jeff

Is this what you mean?

I've added a few notes in blue text to explain how it all works.

Feel free to ask more questions if you need it explaining further.

Hope that's of help.

BSB.

Hi BSB

That looks perfect, thank you so much.

I need to add more subjects to the drop down so will have a play around with it tonight and see if I can add to it successfully.

I would really appreciate it if you could log on here tomorrow to see how I got on.

I really grateful for your help

Thanks

Jeff

Adding new subjects to the drop down is simply a case of adding the new one below the last one on the "Subject List" sheet.
Create a new table and give the correct area of it a name. Only thing I could see problems with is multi word subjects, "English Lit" for example. This can be gotten around by using SUBSTITUTE in the data validation formula.

I will check back tomorrow. (I'm always here!)

BSB.

Hi

Had a quick look and have added a subject, is there a tutorial I can look at online (written or youtube) which would take me through the steps of how this was done as I am keen to learn how to for myself.

Thanks again

Jeff

Erm... I learnt to do it by a combination of trial and error and forums like this one. Full of a wealth of information and not often you will fail to find your answer on here!

If you go searching down the back of the internet for "INDEX/MATCH Lookups", "Dynamic Data Ranges" & "Data Validation" then you will find all you need to know to replicate what I did to your spreadsheet.

BSB.