Multiply a cell range based upon "Drop Down List" selection

[CLewis82]

I have a spread sheet which has a list of regional areas. Each regional area has a specific % increase which is to be added to a subtotal of different costs. Depending on what regional area I select from the drop down list. I would like to be able to multiply a cell range of subtotals or a subtotal cell if easier by the percentage chosen from the drop down list....!?

Northern Ireland @ 3%
Republic of Ireland @ 5%
South England @ 3%
Central England @ 0%
Midlands & Wales @ 2 &
Scotland & Northern England @ 5%


I hope this makes sense....

 

[Deek6t8]

I have a spread sheet which has a list of regional areas. Each regional area has a specific % increase which is to be added to a subtotal of different costs. Depending on what regional area I select from the drop down list. I would like to be able to multiply a cell range of subtotals or a subtotal cell if easier by the percentage chosen from the drop down list....!?

Northern Ireland @ 3%
Republic of Ireland @ 5%
South England @ 3%
Central England @ 0%
Midlands & Wales @ 2 &
Scotland & Northern England @ 5%


I hope this makes sense....

create a table like the one to in the 1st image then in the next screen add formula to cell B2 =IFERROR(INDEX(AreaLookup[%],MATCH(A2,AreaLookup[Area],0)),0) (Area lookup being the name of the table in 1st picture) Then add the formula =B4+(B4*$B$2) to cell C4.

 

[CLewis82]

View attachment 78461View attachment 78462

create a table like the one to in the 1st image then in the next screen add formula to cell B2 =IFERROR(INDEX(AreaLookup[%],MATCH(A2,AreaLookup[Area],0)),0) (Area lookup being the name of the table in 1st picture) Then add the formula =B4+(B4*$B$2) to cell C4.

Thanks Deek, I will try this and report back asap!

 

[CLewis82]

Worked perfectly thanks