Linking a selection in a drop down list to a calc in another cell

[hyweledwards]

Hello there, wondering if anyone could help with this:

I have set up a sales forecasting spreadsheet. Cell A1 contains a drop down
list similar to this:

M1 - Start Trial - 10%
M2 - Finsh Trial - 20%
M3 - Provide Quote - 30%

Cell B1 contains a value e.g. $1000

I was wondering if it would be possible to link the users selection in the
drop down list to a calculation on the value in cell B1.

For example:

A1 contains "M1 - Start Trial - 10%" and cell B1 contains the value $1000.
When the user selects "M1 - Start Trial - 10%" from the drop down list, I
need the spreadsheet to perform a calculation and add B1*0.10 into cell C1.

Then, at a later date, when the user selects the next milestone i.e. "M2 -
Finish Trial - 20%" from the drop down list, I need the spreadsheet to
perform a calculation on the value in B1 (B1*0.20) and change the value in
cell C1.

Is this possible?

Thanks
Hywel

 

[Roger Govier]

Hi Hywel

Try
=B1*N(RIGHT(A1,3))

 

[Bob Phillips]

=B1*MID(A1,FIND("-",A1,FIND("-",A1)+1)+2,99)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Did that work for you Roger? =B1*(--(RIGHT(A1,3))) did for me, but not N.

Also fails on more than 100%, may or may not be an issue.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[hyweledwards]

Hi Roger, thanks for the suggestion, but it didn't work.

I want to change the value in C1 based on changes made to the data in A1
(the drop down list, which contains a list of text based milestones).

The user may select different data from the drop down list over the course
of a year, every time the user does this I need to re calculate the value in
C1. B1 remains a fixed value at all times.

Is it possible?

 

[hyweledwards]

Bob, thanks, this worked

You have made me very happy, cheers

Hywel

 

[Roger Govier]

Hi Bob

I tried =B1*RIGHT(A1,3) which worked perfectly.
I deleted the value in A1 which of course gave me a #VALUE error.
I was about to write an IF clause to deal with A1 being empty, but tried
N(RIGHT(A1,3)) instead.
Made the fatal error of not retrying with the appropriate value in
A1!!!!!