Is it possible to set variable cell validation? (XP)

Topic

New Reply

This is my problem: I wish to records detailed exam results, by recording the marks awarded for each question in each exam paper a student sits.

For example, each question will have a maximum number of marks that can be awarded for that question. So a grid of maximum marks could look like this:
……. | Q1 .|. Q2 .| .Q3 .|. Q4
Ex1 |.. 3 …|.. 4 …|.. 3 …|.. 4
Ex2 |.. 4 …|.. 4 …|.. 3 …|.. 3

The exam papers change each month so the values in the grid will change every month.

The exam results are recorded in a sheet like this:

Name…. | Exam | Q1 | Q2 | Q3 | Q4
Bob Job |.. Ex2 ..|.. 4 .|.. 2 .|.. 2 .|.. 0

What I want is after the Exam is entered in the second column the validation for each question cell will appear as a drop down LIST showing possible marks for that question of that paper. In the example above the drop down list for Bob Job

Reply | Quote

Replies

Hi,

This is a bit complicated, but here goes.

I have a setup like this:

	A	B	C	D	E	F	G
1							Name:Rows
2		Q1	Q2	Q3	Q4		0
3	Ex1	3	4	3	4		1
4	Ex2	4	4	3	3		2
5							3
6							4
7							5
8							6
9	Name	Exam	Q1	Q2	Q3	Q4	7
10	BobJob	Ex2					8
11							9
12							10

In this I defined a few names:

ValList
refers to cell A2

Rows
Refers to cell G2

Questions
refers to cells B2:E2

Exams
refers to cells A3:A4

Now in the cells C10:F10 I have set up validation, List option, using this formula:

=OFFSET(rows,0,0,OFFSET(ValList,MATCH($B10,Exams,0),MATCH(C$9,Questions,0),1,1)+1,1)

Reply | Quote

Fantabbitoesie

Exactly what I wanted and SO FAST. I even understand how OFFSET works now.

Many many thanks; you have saved me days of work.

Graham Luckhurst

Reply | Quote

Fantabbitoesie

Exactly what I wanted and SO FAST. I even understand how OFFSET works now.

Many many thanks; you have saved me days of work.

Graham Luckhurst

Reply | Quote

Hi,

This is a bit complicated, but here goes.

I have a setup like this:

	A	B	C	D	E	F	G
1							Name:Rows
2		Q1	Q2	Q3	Q4		0
3	Ex1	3	4	3	4		1
4	Ex2	4	4	3	3		2
5							3
6							4
7							5
8							6
9	Name	Exam	Q1	Q2	Q3	Q4	7
10	BobJob	Ex2					8
11							9
12							10

In this I defined a few names:

ValList
refers to cell A2

Rows
Refers to cell G2

Questions
refers to cells B2:E2

Exams
refers to cells A3:A4

Now in the cells C10:F10 I have set up validation, List option, using this formula:

=OFFSET(rows,0,0,OFFSET(ValList,MATCH($B10,Exams,0),MATCH(C$9,Questions,0),1,1)+1,1)

Reply | Quote