Dynamically Calc Based on Val Selected from list with All as Option

[cghoga]

Greetings,

Thanks in advance for your time and help.

I have a sheet containing data in tabular format in Excel (2003) that I am using as a data source for another worksheet in the same workbook where I have calculations (product formulas mostly).

At the top of the latter described sheet I have been asked to provide filtering capabilities. 'All' is a filtering option.

For example the list may contain:

All
Red
White
Blue

My question is how do I get my formula to calculate for all rows if the value selected is All or just the Red rows if the value Red is selected from the list?

This may not be possible in one formula, but may require multiple cells/formulas. I am open to anything as long as it works and performance is not adversley impacted.

My sincerest apologies if I have confused. It is difficult for me to articulate.

I currently have Excel 2003, but can get Excel 2007 if this would help.

Thanks for your time and help,

CGH

 

[SkipVought]

Hi,

If your workbook is well designed, in many cases, you can design ONE formula that can calculate values in more than one column.

One way is to use Named ranges. Your All, Red, White, Blue list ought to be a Named Range, like SelectionList. Then, use a Data > Validation -- LIST in the cell when you want the selected value, and name THAT cell ListSelection

Please give a further example of what you want to do. Please be specific, clear, concise and complete.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Here is one way. I have used named ranges based on a table showing Fruit and Qty (quantity). Data validation to populate the FilterCell. =SUMPRODUCT(--(Fruit=myFilterCell),Qty)+SUMPRODUCT(Qty)*--(myFilterCell="All")

Gavin