Help with Data Validation

Hi,

I need some assistance with the following.

I have a spreadsheet which i use as a price list.

Within the price list is a selling price for maintenance which is a % of the license cost.

There is 3 different types of maintenance, each being a percentage.

I need to, with data validation have the ability to pick the maintenance option and it should automaticly populate the tab with the correct amount by taking the selling price and multiplying it by the %.

No matter which one of the 3 maintenance options I pick it should automaticly change the value

Percentage
Gold 18%
Platinum 25%
Silver 16%

So, If I select silver for example, C6 has to automaticly calculate 16% * A9 and populate this in C9. Or, if I pick Gold from the dropdown it has to multiply A9 * 18%

Hope this helpsCopy of Book1.xlsx

Hi,

Check the attached file and see if it works for you

Regards,

Veejar

Hi Fotis, thanks for the reply,

My sheet is a little different as there are some confidential information.

I have changed the colums now to reflect this.

=IF(E9="";0;(D9*(INDEX($E$2:$E$4;MATCH(E9;$A$2:$D$4);0))))

E=the old B
D= the old A
F= the old C

The formula you pasted does not seem to work, did I miss something?

Again, thanks for the help

Book2.xlsx:-) I am blond sometimes

Tried that, but still does not like the formula.

I have attached a separate spreadsheet where they are in the same columns

hope this helps

Hi,

You can also try using a vlookup function as shown in the attached sheet

Regards,

Veejar

Fotis you are a star SIR - the pasting i did only pasted values not formulas :-) you are a star

Hi Fotis,

In true blond spirit I forgot to add the option of 0% if they do not want the maintenance option.

I changed my formulas but it does not want to show 0

=IF(E13="",0,(D13*(INDEX($E$2:$E$5,MATCH(E13,$D$2:$D$5),0))))

Hi, That is one complicated formula, but does not work. I copied and pasted it to F13

Hi Fotis, works fine - not sure why it didn't work thanks for all your help.