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

H

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
 
B

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

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

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

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!!!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top