Dynamic formulas aplied based on critera / drop down list

[D-Ro22]

Hello,

I am attempting to create a drop down list (data validation) and depending on what the user selects I would like have another cell run a calculation summing different values based on that selection. Unfortunately there are too many choices to warrant doing a simple IF statement so I would almost need to have a bank of sample formulas that the selection from the drop down list references. Is there a way to accomplish this?

See the example sheet below, this is obviously a much more simplistic version of what I am trying to accomplish. The idea here would be that every country would be listed on the far left column while various sales criteria would be in each column (a,b,c and so on). Certain jurisdictions define sales differently and may want specific items broken out differently, therefore the user can enter a selection of what to include in the sales line for their country, but the need to be able to quickly change needs to be there for the constantly changing environment

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]Selection
[/TD]
[TD]Sum
[/TD]
[/TR]
[TR]
[TD]Blue
[/TD]
[TD]50
[/TD]
[TD]30
[/TD]
[TD]16
[/TD]
[TD]Add B&C Only
[/TD]
[TD]46
[/TD]
[/TR]
[TR]
[TD]Green
[/TD]
[TD]65
[/TD]
[TD]20
[/TD]
[TD]30
[/TD]
[TD]Add A,B & C
[/TD]
[TD]115
[/TD]
[/TR]
[TR]
[TD]Yellow
[/TD]
[TD]99
[/TD]
[TD]45
[/TD]
[TD]60
[/TD]
[TD]Add A & C Only
[/TD]
[TD]159
[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD]214
[/TD]
[TD]95
[/TD]
[TD]106
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your help!!!!

 

[rcprice]

Hi
With the information you have provided I would do the following:
-Create a data table with all the possible selections/outcomes
-Establish a way of identifying each possible outcome - perhaps using =concatenate(varient1,varient2,etc)
-Insert a vlookup to bring back the required information from the newly created data table =vlookup(identifier,data table range,column reference)

I hope this makes some sense. If you need more detail on the use of vlookups or concatenation, there is plenty of information in Office Help and online.

 

[D-Ro22]

Thanks for the reply. I am aware of all of the functions for which you mention. I am just not certain how to apply them in this case. I would attach a sheet if I were able to, but for whatever reason I am unable.

 

[Smitty]

Welcome to the Board!

You can't upload files here, but you can use the Board's HTML Maker (see the link in my sig) to post screen shots that others can copy to Excel.