excel assigning values
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel - Assigning Values to a drop down list | Excel forum

Excel - Assigning Values to a drop down list | Excel forum

resolvedResolved · Urgent Priority · Version 2007

Daniel has attended:
Word Intermediate course
PowerPoint Intermediate Advanced course
Excel Intermediate course

Excel - Assigning Values to a drop down list

Hi there,

I have created a drop down list (using data validation) which contains a list of injuries (Head, Arm, Leg, Hands, Feet).

When an option is chosen I want to link this to a cell where the result will appear as "1". I suppose the question would be how to assign a vlaue to an option in a drop down list?

Thanks for your swift response.

Kind Regards,

Daniel

RE: Excel - Assigning Values to a drop down list

Hi Daniel

Thank you for your question.

I think you can achieve what you are looking for by creating a nested IF function that looks at what has been entered using the dropdown list, and enters a corresponding value depending on what has been selected.

I've attached an example spreadsheet - the dropdown list has been attached to some cells in column A, using data validation. The IF function has been entered into cells in column B.

I hope this helps - please reply to this post if you have questions.
Amanda

Attached files...

dropdown list and corresponding value.xls

RE: Excel - Assigning Values to a drop down list

Amanda,

That was fantastic. Really helpful. I had a feeling that was what I needed to do but had some issues with the code, so it was good to see a real live version of it.

I have another question for you regarding this absence spreadsheet.

One of the fields is for paid leave, the reasons for which are so random that the possibilities could never be covered fully in a drop down list.

So what I need is a text box that allows free text to be added into it but I need this box to link into the rest of the spreadsheet and to be able to assign a value to the text entry.

Does that make sense?

It would be great if you could help as those were the two key problems I anticipated facing and having overcome one would love to tick the other off my list.

Kind Regards,

Daniel

RE: Excel - Assigning Values to a drop down list

Great, I'm glad we managed to solve one problem.

The other could be a little more tricky - how do you imagine assigning a value if there could be many possible entries? Would it be, for example, to assign 1 if a reason has been entered; and 0 if not?

Amanda

RE: Excel - Assigning Values to a drop down list

Amanda,

Sorry for taking so long to respond.

That's about the crux of it. If someone has been off for the day and we add a reasdon, I want that to be assigned "1" as they have had 1 day off work and if nothing is entered then keep it as 0.

Any thoughts?

Thanks for all your help by the way.

RE: Excel - Assigning Values to a drop down list

Hi Daniel

No problem, happy to help where I can :)

I've reattached the example Excel file I included in response to your earlier post, with an additional sheet (Q2) which shows the formula I think you will need to use.

The formula is shown in the yellow cells - basically it looks at the cell in the 'Reason' column to see if it is blank (that is the ISBLANK part), then leaves the cell blank (indicated by the "") if there is nothing in the reason column, but enters 1 if there is a reason entered.

I hope this helps.
Amanda

Attached files...

dropdown list and corresponding value.xls

RE: Excel - Assigning Values to a drop down list

I think that's exactly what I need Amanda.

I'll have a play around with and give you a bell if I'm having problems.

Thank you so much for your help with this.

RE: Excel - Assigning Values to a drop down list

Great, please post again if need be.

Kind regards
Amanda

RE: Excel - Assigning Values to a drop down list

Hey there Amanda,

I didn't think I'd be pestering you so soon.

I've done the open text box, so fantastic.

But I'm having issues with adding formulas for the drop down menu. I have five drop down menus (D4 - H4) and want to add up the values of each in a totals box.

I've added two formulae together - D4 and E4 (the formula for which I've included below) using the same formula and adding a plus sign but it doesn't want to accept the changes I make and I can't see where I'm going wrong.

Again thanks for your help with this.

=IF(D4=" ",0,IF(D4="Accident",1,IF(D4="Allergic Reaction",1,IF(D4="Ankle",1,IF(D4="Back",1,IF(D4="Beaten Up",1,IF(D4="Bladder",1,IF(D4="Car Accident",1,IF(D4="Chest Infection",1,IF(D4="Chicken Pox",1,IF(D4="Cold",1,IF(D4="Depression",1,IF(D4="Ear Infection",1,IF(D4="Eye Infection",1,IF(D4="Flu",1,IF(D4="Food Poisoning",1,IF(D4="Gastric Sickness",1,IF(D4="Gastroenteritis",1,IF(D4="Head",1,IF(D4="Jetlag",1,IF(D4="Kidney Infection",1,IF(D4="Knee",1,IF(D4="Mental Issues",1,IF(D4="Operation",1,IF(D4="Pancreatitis",1,IF(D4="Root Canal",1,IF(D4="Salmonella",1,IF(D4="Shoulder",1,IF(D4="Sick",1,IF(D4="Sickness",1,IF(D4="Sore Throat",1,IF(D4="Sprained Ankle",1,IF(D4="Stomach Ache",1,IF(D4="Stress",1,IF(D4="Throat Infection",1,IF(D4="Tonsil",1,IF(D4="Toothache",1,IF(D4="Virus",1,""))))))))))))))))))))))))))))))))))))))+IF(E4=" ",0,IF(E4="Accident",1,IF(E4="Allergic Reaction",1,IF(E4="Ankle",1,IF(E4="Back",1,IF(E4="Beaten Up",1,IF(E4="Bladder",1,IF(E4="Car Accident",1,IF(E4="Chest Infection",1,IF(E4="Chicken Pox",1,IF(E4="Cold",1,IF(E4="Depression",1,IF(E4="Ear Infection",1,IF(E4="Eye Infection",1,IF(E4="Flu",1,IF(E4="Food Poisoning",1,IF(E4="Gastric Sickness",1,IF(E4="Gastroenteritis",1,IF(E4="Head",1,IF(E4="Jetlag",1,IF(E4="Kidney Infection",1,IF(E4="Knee",1,IF(E4="Mental Issues",1,IF(E4="Operation",1,IF(E4="Pancreatitis",1,IF(E4="Root Canal",1,IF(E4="Salmonella",1,IF(E4="Shoulder",1,IF(E4="Sick",1,IF(E4="Sickness",1,IF(E4="Sore Throat",1,IF(E4="Sprained Ankle",1,IF(E4="Stomach Ache",1,IF(E4="Stress",1,IF(E4="Throat Infection",1,IF(E4="Tonsil",1,IF(E4="Toothache",1,IF(E4="Virus",1,""))))))))))))))))))))))))))))))))))))))

RE: Excel - Assigning Values to a drop down list

Hi Daniel

I think the problem is that you are trying to nest too many IF functions - you can nest up to 7 IF statements one inside the other.

Without obviously having seen the spreadsheet, I would have assumed that you pick an item from each dropdown menu, and a corresponding value will appear in the next cell over, then you would just need to use a SUM function in another cell to add together the values?

Amanda

RE: Excel - Assigning Values to a drop down list

I thought that may be the problem but just needed confirming.

I'll give it a go to try and resolve the issue.

Again, thanks for all your help.

Dan

RE: Excel - Assigning Values to a drop down list

OK, let me know how you get on.

Kind regards
Amanda

RE: Excel - Assigning Values to a drop down list

Hi there Amanda,

I hope all is well.

Managed to sort out that problem but am facing another issue!!!

Across one row, we have hundreds of different reasons as to why people have been off sick which have been input as free text.

If the person has only taken a half day, the text will begin with a "/".

Is there anyway of adding a formula that will calculate which of the boxes begins with the "/"? I believe I'd have to use an IF formula with perhaps a code function as well but am unsure of how the 2 would fit together?

As always, your help is welcomed.

Dan

RE: Excel - Assigning Values to a drop down list

Hi Daniel

I'm assuming that the / would be the very first thing that would be entered into the cell, without a space or anything else in front of it?

If so the attached example should work. Anytime there is a / as the very first character in the cell, a 1 should display next to it.

Let me know what you think.
Amanda

Attached files...

find function.xls

RE: Excel - Assigning Values to a drop down list

Super stuff Amanda.

I've just had a sit down with girls here and they would like to be able to change the value that appears.

Is there anyway of changing the "1" to a "0.5"?

You've been a real help throughout this and I'm not sure what I would have down without you.

Dan

RE: Excel - Assigning Values to a drop down list

Hi Dan

I did think about that, but my thought was you could use a formula to add up the column of 1's and then divide by 2...ok or not?

Amanda

RE: Excel - Assigning Values to a drop down list

I'm not sure whether that would cover it?

The issue we have is that this is for amount of days leave.

To describe the spreadsheet, we have days of the week along the top and types of leave down the side. The total box on the right will add up the amoutn of days taken.

In this particular row, we want to be able to input free text. But to be able to distinguish between full and half days.

This is why I asked the question about the forward slash. I thought if we could use this to differentiate between the two that would be the key.

I think we're nearly there with the most recent formula but I tried adding them up and it wouldn't accept it.

I'm struggling a little with this and haven't been able to manipulate the formula as I normally would.

Help!

Thanks again.

RE: Excel - Assigning Values to a drop down list

Hi Daniel

Sorry it's taken me a few days to get back to you, I've attached a revised version of the previous example showing 0.5 next to the cells containing a /

Hope this helps.
Amanda

Attached files...

find function.xls

RE: Excel - Assigning Values to a drop down list

Nearly there.

Ok, so using the most recent formula, I can do a half day and a full day (I've stuck a 1 in the " ").

Using the same formula, is there anyway of returning a zero if nothing is entered into the cell?

I'm hoping this is the final piece of the jigsaw?

RE: Excel - Assigning Values to a drop down list

Hi Dan

Yes, it will be a nested IF function.

=IF(ISBLANK(A2),0,IF(ISERROR(FIND("/",A2)),1,0.5))

First Excel needs to test to see if the cell is blank - if it is, it will enter 0.

If not (i.e. there is an entry in the cell), it will then check to see if it finds a / in the cell - if it doesn't find a / then it will enter 1.

The only other alternative is that the cell contains a / representing a half day therefore Excel will enter 0.5

I hope we finally have a resolution for you :)
Amanda

Fri 12 Dec 2008: Automatically marked as resolved.

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

See also:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
best training excel intermediate and other training courses.

Excel tip:

Convert Text to Columns in Excel 2010

If you have a cell in your Excel spreadsheet that contains a lot of text and you want to divide it into separate columns, this can only be done if there is a logical character which separates the text, for example, a comma.

Select the cells you would like to convert. On the Data tab, click Text to Columns. Choose the format of your current data.

Select Delimited if the text contains a logical character otherwise select Fixed Width if there are a certain number of spaces between each field.

Click Next when a preview of the data appears. Then select the type of character that separates the various fields. If the character is not listed, select Other and enter the character.

Click Next again and then choose the format for each of the columns. Select the column heading in the Data preview and then select a data type from the Column data format options.

Click Finish and the text will appear in several columns.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.11 secs.