Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hi,

    This is a bit complicated, but here goes.

    I have a setup like this:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center>G</td><td align=center valign=bottom>1</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td valign=bottom>Name:Rows</td><td align=center valign=bottom>2</td><td align=right valign=bottom></td><td valign=bottom>Q1</td><td valign=bottom>Q2</td><td valign=bottom>Q3</td><td valign=bottom>Q4</td><td align=right valign=bottom></td><td align=right valign=bottom>0</td><td align=center valign=bottom>3</td><td valign=bottom>Ex1</td><td align=right valign=bottom>3</td><td align=right valign=bottom>4</td><td align=right valign=bottom>3</td><td align=right valign=bottom>4</td><td align=right valign=bottom></td><td align=right valign=bottom>1</td><td align=center valign=bottom>4</td><td valign=bottom>Ex2</td><td align=right valign=bottom>4</td><td align=right valign=bottom>4</td><td align=right valign=bottom>3</td><td align=right valign=bottom>3</td><td align=right valign=bottom></td><td align=right valign=bottom>2</td><td align=center valign=bottom>5</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>3</td><td align=center valign=bottom>6</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>4</td><td align=center valign=bottom>7</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>5</td><td align=center valign=bottom>8</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>6</td><td align=center valign=bottom>9</td><td valign=bottom>Name</td><td valign=bottom>Exam</td><td valign=bottom>Q1</td><td valign=bottom>Q2</td><td valign=bottom>Q3</td><td valign=bottom>Q4</td><td align=right valign=bottom>7</td><td align=center valign=bottom>10</td><td valign=bottom>BobJob</td><td valign=bottom>Ex2</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>8</td><td align=center valign=bottom>11</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>9</td><td align=center valign=bottom>12</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>10</td></table>

    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)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Jan 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •