new question and answer form - turn words into a score - multi choice answers cell

Hi all sorry if this has been answered before but I could not find a thread.

1 I am creating a Q and A form and the answers will then generate a score. i.e Daily = 5, most days 3, most weeks = 2, most months = 1 and rare/ 1st call = 0 (this is the longest) What would be the formula please have tried a few but can not get more than one word to equal a number rather than the 6 I need?

2 there are 6 questions to be scored and then they need to be added up to give a grand total.

3 I have seen another form that you can double click on the cell and you then get a multiple chioce you can chose from i.e daily, most days etc. Is this a formula or a function I am not aware about.

Thanks in anticipation
Pete

Is something like the attached what you're looking for?

I've set up columns A:F as being the 6 questions and used data validation lists for rows 2:11.

I'm not familiar with Excel 2000, so I've tried to keep everything really simple.

Yes thank you almost there.
I believe I can sort out the formula from there thanks and re jig the form.
How do you get the drop down menu please?

I've listed the options in cells L1:L5 and named that range 'Score'

You can then use Data Validation (no idea where it is in your version of Excel, sorry, but it will be in one of the menus) with a setting of 'List' and set the list to:

=Score

so far so goo.

data validation is under the Data drop down menu - for those who may view this later.

#big thanks#

I think I have added a version of the form i managed to do hope this helps anyone else.