Formula & calculation change based on drop down selection

[glee323]

Hello, I did a search on drop down and looked through all 12 pages but couldn't find a post with this same question.

I am using Excel 2007 and Windows XP - would appreciate help with changing a formula based on a drop-down choice. Right now my spreadsheet looks like the attached. I've put in the formula for you to see.

Instead of having a volume and cost column, I would just like one column to say Values and the values to change (using either the cost or volume formula) based on the drop-down choice in B2. So, if cost was chosen from the drop down then D6 would show the result using the current formula in E6.

Right now there are about 12 columns for each, 24 in total and I'm using a toggle button to show/hide either data set.

Is this possible?

Link to spreadsheet - http://www.box.net/shared/emj2tjguoct6tsk9j6hf

 

[nigelk]

A simple "if" formula should be enough.


=IF(B2="Volume",SUMIF('R'!G:G,'Trend Summary'!C10,'R'!L:L)/Admin!$K$4,SUMIF('R'!G:G,'Trend Summary'!C10,'R'!K:K)/Admin!$K$4)

 

[glee323]

thank you so much! I must have been looking at it too long. after i took a nap i realized an if statement would work but kept getting errors. your formula worked beautifully!

 

[jasonb75]

Another way would be

=SUMIF('R'!G:G,'Trend Summary'!C10,INDEX('R'!K:L,,MATCH(B2,{"Cost","Volume"})))/Admin!$K$4