Drop Down List

[hombreazul]

Hello,

I hope you all can help me. I have a sheet in the following format,
where under the assignment header would be listed a grade associated
with that assignment.


Student Name| Assignment 1 | Assignment 2|.....
xxxxxxxx |XXXXXXXXXXXXX|XXXXXXXXXXXX|
xxxxxxxx
xxxxxxx
xxxxxx



What I am trying to figure out is how to create a table where the
assignment columns become dropdowns, such that I can say, switch the
first column to assignment 5, or whatever, and the right data
populates below. Is that possible?

Did I even explain that right? Thanks for your help.

 

[Alan]

Hello,

I hope you all can help me.  I have a sheet in the following format,
where under the assignment header would be listed a grade associated
with that assignment.

Student Name| Assignment 1 | Assignment 2|.....
xxxxxxxx       |XXXXXXXXXXXXX|XXXXXXXXXXXX|
xxxxxxxx
xxxxxxx
xxxxxx

What I am trying to figure out is how to create a table where the
assignment columns become dropdowns, such that I can say, switch the
first column to assignment 5, or whatever, and the right data
populates below.  Is that possible?

Did I even explain that right?  Thanks for your help.

Everything is possible (well most things) but you may have to turn to
VBA. Your requirements aren't very clear though so I can't even have a
stab at an answer.
A.

 

[Gord Dibben]

See what you can do with the info at this site.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP

 

[hombreazul]

See what you can do with the info at this site.

http://www.contextures.on.ca/xlDataVal02.html

Gord Dibben     MS Excel MVP

Thanks so much for your suggestion. I looked at contextures and
several samples approximated what I would like to do, but never quite
got there.

What I would like to do is have the A2 column show a list of
students,with the header "students". The headers for A3 and so forth
would be the dropdown list and would be assignment names. Ideally, I
would be able to select an assignment from the dropdown list and the
scores from that assignment would populate below the header and
correlate with the student name.

I hope that explanation was better than my first attempt. Thank you.

 

[Alan]

Thanks so much for your suggestion.  I looked at contextures and
several samples approximated what I would like to do, but never quite
got there.

What I would like to do is have the A2 column show a list of
students,with the header "students".  The headers for A3 and so forth
would be the dropdown list and would be assignment names.  Ideally, I
would be able to select an assignment from the dropdown list and the
scores from that assignment would populate below the header and
correlate with the student name.

I hope that explanation was better than my first attempt.  Thank you.- Hide quoted text -

- Show quoted text -

I think that I now have an idea of where you coming from but
describing the answer is more complicated that trying to describe the
question!

I would use VBA: there is a disconnect between your dropdown lists
(which are only effectively column headers) and the data that is held
in the table. I think that there are three steps to the solution.

1. Data Entry

I would suggest using a User Defined Form to allow the marks for
each student to be input to the workbook. Marks that have already been
entered would appear in the UDF automatically and would still be open
for editing: new marks for assignments could be typed in as required.

2. The Data Library

The data entered above would be written to a "veryhidden"
worksheet. Column A would contain a list of student names: Row1 would
contain the assignment titles that will be used to populate your
dropdown boxes on the Reporting worksheet. The body of the worksheet
would contain an array of the marks given.

3. Data Reporting

Each of the dropdown boxes would contain a list of the assignment
titles taken from the above data library. When assignment is selected,
the appropriate column in the Library worksheet would be located and
the marks for each student would transferred to the report sheet.

I hope that describes what you want? The question is whether you have
the knowledge to do it - it isn't complicated but you do need to know
your way around VBA.

A.