Hi I am a Teacher trying to deal with new scoring criteria for students years 7-11.

Q:Is it possible to input in 1 cell numbers from 0 -47 in a drop down menu "easy bit" but each numbers represent different numerical scoring values.

as an example:

If I enter the no 12 in cell A2 from the drop down menu I need that to show automatically in A3 as the number 2

Additional Example Scoring Criteria:

If I enter the no 16 in cell A2 I need that to show automatically in A3 as the number 3

If I enter the no  33 in cell A2 I need that to show automatically in A3 as the number 6

and so on!

I hope some one can help this seems a simple task but I can not work it out I have tried basic WHAT IF formulas and nests of formulas but no success.

Any help or guidance would be great.

Regards

Science Man

Create a 2 column table of the values somewhere in the workbook so that one column contains the 0-47 and the other column contains the numerical scoring values relative to the number. (Table can be on another worksheet and after development is finished that worksheet can be hidden if desired.)

Then use VlookUp formula in column "A" cells so they look up the value in the dropdown and find the match in the table. See Help for more information on VlookUp function. However, just a small tip with the function. The Table Array part of the formula must be in absolute format with the $ signs. Again see Help for more information on Absolute and Relative formats.

If you don't understand then upload a sample workbook to OneDrive (Just a sample without any sensitive data) and I or someone else will have a look at it for you. (It is getting late in my part of the world so might be tomorrow before I can answer again.)

Guidelines to upload a workbook on OneDrive:

1. Zip your workbooks. Do not just save to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder.)
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Do NOT fill in the form; Select "Get a Link" on the left side.
10. Click the button "Create a Link"
11. Click in the box where the link is created and it will highlight.
12. Copy the link and paste into your reply on this forum.

Hi Science Man,

I tried to create a sample file as shown in the following figures.

I created a drop down list with the source data from Column Number.

And in cell B2, I created a formula: =VLOOKUP(A2,E:F,2,FALSE), it returned Column Scour data depend on Column Number.

You can download the sample file from this link:http://1drv.ms/1MB7Vh6

If my understanding is incorrect, please be a bit more precise to explain your requirement so that I can get more accurate solutions to this problem.

Im glad to help and follow up your reply.

Regards,

Emi Zhang
TechNet Community Su

Hello OssieMac

Thanks for the prompt response, with your help I have managed to solve the problem.

I really appreciate your time and input.

Keep up the good work

Kind Regards

Science Man

Hi Emi Zhang

Along with a previous reply from OssieMac and your terrfic downloadable example I have solved the problem.

I have also utilised the formulas that you created in other elements of the grading scores for the students.

Thanks so much, have agreat day

Regards

Science Man