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?

Msgbox Val(whatever)

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)

Select allOpen in new window

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

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

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).

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?

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.

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)?

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?

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.

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.

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.

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".

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

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

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

Copy and paste on phone screen not always reliable!!

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?

Sorry, missed a bracket:

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

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;"")

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.

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.

Try this:


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

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.

Can you upload a sample workbook to look at?

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.

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

Here are my values:

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

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