Expiring Todayâ€”Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

In Excel 2007 in a drop-down list, how do I get a value selected in the drop-down to be treated as a specific value?

Posted on 2016-09-30
Medium Priority
73 Views
In the drop-down list, I have these values:

20 m
40 m
60 m
80 m
100 m
120 m
140 m

I want "20 m" to be treated as the figure "10" if "20 m" is selected in the drop-down (so that another formula retrieves the value 10).
0
Question by:hermesalpha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 15
• 11
• +1

LVL 49

Expert Comment

ID: 41823380
Msgbox Val(whatever)
0

LVL 32

Expert Comment

ID: 41823393
Not sure what exactly you are trying to achieve....
Assuming your drop down list is in cell A2 and you have selected "20 m" and you want a formula to return 10 in this case otherwise return whatever is selected in the drop down.
Try this..

``````=IF(A2="20 m",10,A2)
``````
If that is not what you are trying to achieve, please elaborate your requirement.
0

LVL 33

Expert Comment

ID: 41823428
Assuming you are using Data Validation dropdown list, go to your source list and change to proper numbers but format with a custom format  0 "m". The values will then show in the dropdown as 10 m, 20 m etc but when selected the value selected will be a number.

Apply the same custom format to the validation cell.

See attached.

Thanks
Rob H
Validation-list.xlsx
0

Author Comment

ID: 41823507
Actually I need the value to be half of "20 m" and so on. So that "20 m" counts as "10" etc.

Subodh's solution could have worked, but I need this conversion to take place in the same cell where the drop-down list is. So that "20 m" in the drop-down list in cell A2 is converted/counted as "10" (and written out as "10") in cell A2 (where the drop-down list is).
0

LVL 33

Expert Comment

ID: 41823555
Apologies, I had incorrectly assumed the references to 10 were typos.

You can still use the Custom Format on the selection list and it will still show in the list with the m.

How about adjusting the onward formula so that it just halves the value selected?
0

LVL 33

Expert Comment

ID: 41823568
For the selected value to be halved in the selected cell will require some VBA in the Worksheet Change event code.

However, when VBA runs it clears the Undo History.
0

Author Comment

ID: 41823599
Do you mean it's possible I have these "20 m", "40 m" etc. in the drop-down list, and then when I select for example "20 m" it will display "10" in the same cell (and count as 10)?
0

Author Comment

ID: 41823608
Sorry, it's not necessary to display "10" in the same cell, it can display "20 m", but it must count as "10". So that "20 m" equals to "10". Is that possible without using VBA?
0

LVL 33

Expert Comment

ID: 41823631
Yes, if selection in cell F3 and onward formula refers to F3 change formula to refer to F3/2

That is in addition to doing the custom format like I had in the file I uploaded.
0

Author Comment

ID: 41823642
Can I do without any external list, only use listing inside the data validation box? Probably not, as I need to do the custom format.
0

LVL 33

Expert Comment

ID: 41823670
If you have the m in the list values then the onward formula will have to allow for it.

=TRIM(LEFT(F3,LEN(F3)-1)*1

The *1 forces excel to reevaluate the text result into a number. Might not need it as onward formula should have same effect.
0

Author Comment

ID: 41823730
The problem now is I'm not allowed to enter the formula in the same cell where I have the drop-down list.

"Onward formula", do you mean Subodh's formula?:

=IF(A2="20 m",10,A2)

This is how my Excel looks like:

In F19, I have this formula:

=IFERROR(F14-(F20*2)+Q22+Q23+Q24+Q25;"")

Then, in Q22, I want to have this drop-down list with the following items (each of them only "worth" half), which should be counted as a number (without the "m") half of what is displayed:

20 m
40 m
60 m
80 m
100 m
120 m
140 m

If I select "20 m" in this drop-down list in Q22, then the formula in F19 should equal this "20 m" with "10".
0

LVL 33

Expert Comment

ID: 41823758
=IFERROR(F14-(F20*2)+TRIM(LEFT(FQ22,LEN(Q22)-1)*0.5+Q23+Q24+Q25;"")
0

LVL 33

Expert Comment

ID: 41823762
Thats assuming you still have the values in the data validation input with the m included and not the custom format.
0

LVL 33

Expert Comment

ID: 41823772
Just noticed the FQ in the formula, should only be Q.

Copy and paste on phone screen not always reliable!!
0

Author Comment

ID: 41823817
I tried your function now, but it says I have entered too many arguments for this function. This is what I have entered (I use semicolon instead of comma):

=IFERROR(F14-(F20*2)+TRIM(LEFT(Q22;LEN(Q22)-1)*0.5+Q23+Q24+Q25;"")

By the way, in this drop-down list I have a "-" also first in the list (so that I can go back to displaying nothing in the cell from having selected for example "20"). If I have selected "-" in the drop-down list, how can I exclude this cell from the summation of cells by the formula in F19?
0

LVL 33

Expert Comment

ID: 41825970
Sorry, missed a bracket:

=IFERROR(F14-(F20*2)+TRIM(LEFT(Q22;LEN(Q22)-1))*0.5+Q23+Q24+Q25;"")
0

LVL 33

Expert Comment

ID: 41825974
For the "-" option, do you have an actual dash in the cell or is it zero formatted as a dash.

I would recommend that it is a formatted zero then it will have no impact on the result anyway, 0 * 0.5 is zero and then adding it to the other cell values will not change the result.

If its a dash:

=IFERROR(F14-(F20*2)+IF(Q22="-";0;TRIM(LEFT(Q22;LEN(Q22)-1))*0.5)+Q23+Q24+Q25;"")
0

LVL 33

Expert Comment

ID: 41826442
Just recalled from earlier comments that your list is within the DV setup window rather than a source range list so will be an actual "-".

Therefore, you need the formula above.
0

Author Comment

ID: 41866012
This works great Rob, thanks! Just one thing though: when I select "-" in the drop-down list, the formula displays nothing in F19 (it needs to display the other calculations, in F14, F20 etc.).

Worth mentioning: in my data validation list, I have 4 blank spaces before the "-" and 5 blanks spaces after it. This "-" comes first in the data validation list.
0

LVL 33

Expert Comment

ID: 41866034
Try this:

=IFERROR(F14-(F20*2)+IF(TRIM(Q22)="-";0;TRIM(LEFT(Q22;LEN(Q22)-1))*0.5)+Q23+Q24+Q25;"")
0

Author Comment

ID: 41866529
Hi,

It didn't work; if I select "-" in the drop-down list or don't select anything in the drop-down list, I get nothing in F19 (even though there should be the result of a calculation there on "496").

This function is for adjusting the time for harness racing horses when they have a penalty on for example 20 metres when starting the race. A horse is approximately 2 metres long, and the calculations for time are based on horse lengths. So 20 metres penalty in the start means 10 horse lengths behind.
0

LVL 33

Expert Comment

ID: 41866673
Can you upload a sample workbook to look at?
0

Author Comment

ID: 41867165
I tried deleting "IFERROR", like this:

=F14-(F20*2)+IF(TRIM(Q22)="-";0;TRIM(LEFT(Q22;LEN(Q22)-1))*0.5)+Q23+Q24+Q25

But I get a "#VALUE!" error then.
0

LVL 33

Expert Comment

ID: 41868326
I have just tried that formula and it works, I get a result of zero because I have nothing in the other cells.

Therefore, maybe it is the content of the other cells which is causing the issue. What is in the following cells:

F14
F20
Q23
Q24
Q25
0

Author Comment

ID: 41876937
Here are my values:

F14 500 (no formula, just a value)
F20 3 (no formula, just a value)
Q23 (nothing)
Q24 (nothing)
Q25 (nothing)
0

LVL 33

Accepted Solution

Rob Henson earned 2000 total points
ID: 41876955
With Q22 set to "     -    "  (5 spaces, - , 4 spaces) and the other values populated it works fine, gives result of 494. If Q22 is blank I also get the #Value! error. So, slight amendment to allow for Q22 being blank:

=F14-(F20*2)+IF(OR(TRIM(Q22)="-";Q22="");0;TRIM(LEFT(Q22;LEN(Q22)-1))*0.5)+Q23+Q24+Q25
0

Author Closing Comment

ID: 41878238
Thanks Rob, this formula worked perfect. Now the formula is calculated even if I choose - in the drop-down list.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Suggested Courses
Course of the Month11 days, 2 hours left to enroll

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.