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
28
51 Views
Last Modified: 2016-11-07
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
Comment
Question by:hermesalpha
  • 15
  • 11
  • +1
28 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41823380
Msgbox Val(whatever)
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
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)

Open in new window

If that is not what you are trying to achieve, please elaborate your requirement.
0
 
LVL 31

Expert Comment

by:Rob Henson
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

by:hermesalpha
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 31

Expert Comment

by:Rob Henson
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 31

Expert Comment

by:Rob Henson
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

by:hermesalpha
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

by:hermesalpha
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 31

Expert Comment

by:Rob Henson
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

by:hermesalpha
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 31

Expert Comment

by:Rob Henson
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

by:hermesalpha
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 31

Expert Comment

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

Expert Comment

by:Rob Henson
ID: 41823762
Thats assuming you still have the values in the data validation input with the m included and not the custom format.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 31

Expert Comment

by:Rob Henson
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

by:hermesalpha
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 31

Expert Comment

by:Rob Henson
ID: 41825970
Sorry, missed a bracket:

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

Expert Comment

by:Rob Henson
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 31

Expert Comment

by:Rob Henson
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

by:hermesalpha
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 31

Expert Comment

by:Rob Henson
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

by:hermesalpha
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 31

Expert Comment

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

Author Comment

by:hermesalpha
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 31

Expert Comment

by:Rob Henson
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

by:hermesalpha
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 31

Accepted Solution

by:
Rob Henson earned 500 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

by:hermesalpha
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now