Formulas depending on which item selected from drop down list

[Caroline777]

I have created a drop down list with 3 options of profit margins 20,30 & 40. Depending on what is selected I then want to take a net price from a different column and multiply it by either 1.25, 1.4 or 1.6 to get a retail price in another column and cannot figure out how to do this? Columns I refer to highlighted in red

[nick.williams]

Does this do what you're after in Q2?

=O2*VLOOKUP(P2,{20,1.25;30,1.4;40,1.6},2,0)

I'd normally have the array (the bit in {}) in cells somewhere and refer to that - it makes it much easier to edit.

[Caroline777]

Thank you very much that has worked perfectly!

[Caroline777]

As you are so good can you help me with another issue:
All of our parts are labelled RDK0001 etc and we have a quantity in stock column
On a separate sheet we have a booking out sheet and I would like (if possible) to have it so that when a part no is booked out it decreases the stock total on the parts list sheet

[JeteMc]

Try the following in cell E2 on the BOOKING OUT SHEET:

Please Login or Register  to view this content.

Once activated drag the fill handle down to cell E3.
Let us know if you have any questions.

[Caroline777]

Hi Thankyou for this - sorry for the delay I have been away

I have copied this and it decreases the stock remaining on the booking out sheet. However what I need is when we book out a part on booking out sheet column D that it decreases the stock remaining amount on Parts List column K depending on which part number is booked out as well as decreasing the stock remaining on booking out sheet (however may not need that column as well tbh) Hope this makes sense

[JeteMc]

I believe that in order to do what you describe there will have to be another column that displays the initial amount in stock for each part on the Parts List sheet.
If a formula is placed in column K (Parts List sheet) then it will overwrite the numbers presently in that column.
In the attached file the initial stock amount is in column S.
The formula that populates column K is: =IF(S2="","",S2-SUMIFS('BOOKING OUT SHEET'!D$2:D$3,'BOOKING OUT SHEET'!B$2:B$3,A2))
Let us know if you have any questions.

[Caroline777]

Thank you very much that is great!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.