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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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)
 
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)
 
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?
 
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!!!!!
 
Back
Top