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
68 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
[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
28 Comments
 
LVL 47

Expert Comment

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

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 33

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 33

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 33

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 33

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 33

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 33

Expert Comment

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

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
 
LVL 33

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 33

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 33

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 33

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 33

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 33

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 33

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 33

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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ActiveX component can't create object VBA how to fix it 6 60
Excel shared spreadsheet 12 38
copy down array 24 36
Excel - query for count of numbers in a range 8 22
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 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