Excel (2008 for Mac) - Validation lists like a combo box?

[Welshbird]

I know this title doesn't really explain what I'm trying to do here, but I've been asked to help do some charting and data validation for a local school.

So, here's the thing. School results actually are non-numeric unfortunately. They range from 1C (through 1B etc) up to 5B.

There are actually 'numbers' behind these, but they don't mean anything to the school staff.

So, what I was hoping to do was to display 1C or whatever whilst actually graphing the actual figures.

I'd also like ideally them to be able to enter the data using a drop-down list from a validation list. So far I can't see how to use a validation list with two columns.

I think I could get away with using the validation list as a drop-down and the next column (hidden) as a vlookup, but that seems a complex method and prone to potential errors.

Any thoughts really gratefully received.

(I'd prefer to do this in Access, however they don't have a license and they also have never used it so it isn't something they'd be comfortable with. Also, Office 2008 for Mac doesn't have Access....)



Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[xlbo]

Hi Fee - how do the grades (1C or whatever) relate to numbers and how would they need to be graphed?


In terms of the data validation, you are correct in that a validation list cannot have 2 columns

the best method for entering the data depends on how the data needs to be entered....if it is a list that will kepp expanding then yes, using a vlookup to place the number next to the grade will be a little complicated - you could embed a listbox control int eh worksheet or it may be better to use a bit of VBa to pop up a smal userform when they click on a certain column which will then enter both sets of data for you. If the list is pretty static then using a vlookup should be ok - the issue with the vlookup would only really be with needing to move the formula down a list...


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Welshbird]

The values and grades are as follows:
[tt]
BW 3
W 5
1C 7
1B 9
1A 11
2C 13
[/tt] etc.

The aim is to create a line graph comparing either two children against each other, or a child against its expect results to is basically a line.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Just pondering 'aloud' - no real reason why I can't give them one big sheet to enter the data in the format they recognise, and then have vlookup-type sheets to do the graphs off.

(I still can't work out my graphs but maybe I'll post summat else about that!)

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[xlbo]

So would each child have multiple grades / values and you need to view the total sum of value (based on multiple grades) ? Seems like there should also be a data element for the subject

If you had a layout like

Child,Subject,ActGrade,ActValue,ExpGrade,ExpValue

you should be able to chart up pretty much anything....


You could create a master sheet which holds each child / subject combination and then set up your vlookups to work off the data validation list to return the value for each selected grade (actual or expected)

That way you would not need to worry about dynamically adding formulae and it would be pretty simple to sue a pivot table / sumifs / countifs to summarise for each child on a seperate sheet...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question