Extract values if condition is satisfied

I know you mentioned a formula but have you considered using a Pivot Table? And if so why do you want a formula rather than a PT?

Hi bjnockle,

Find the attached that has some VBA code behind the worksheet and in a module. If you change any of the Orange values (using validation) it triggers the event code behind the worksheet. This code calls the "AdvFilter" code that does an Advanced Filter and gives what you have selected in orange. If you want all then select a Blank instead or a word (Dell or Acer).

See if this works for you.

Like Richard, I am curious why a formulaic solution is needed over a Pivot Table. Once the PT is set up, it is only a case of choosing your criteria and it automatically updates. Help us to understand your thought process on this issue.

See attached for PT results.

Originally Posted by bjnockle

I would like to accomplish this task by formula.

The obvious question is why?

I'm with the others on this. Listing only those items that match the criteria could probably be done with array formulas (I'm not an expert with array formulas so I wouldn't be able to help with that). Alternatively you could get all the data that matches the criteria without an array formula, but you would have to list all the products and then have blank rows for those products that don't meet the criteria. A pivot table will give you what you need quickly and easily.

Hey Alan,

He could even use Slicers to get his answer and not even need dropdowns.

No Formulas needed

Marvin,

I concur. Thanks for adding it.

Alan

Originally Posted by bjnockle

MarvinP: Great suggestion. I am familiar with both PT and slicers functionality in Excel. however, would like to pull the values using a formula. Thanks.

Hi,

I don't see where you have enlightened us as to why you prefer a formula other than to say " I would prefer a formula to accomplish this task in case there is need to modify the data."

I can't honestly see why you should consider expanding or modifying data would be a problem for a Pivot Table.
First of all if you are modifying data, (e.g. general layout or relationships) then it's more than likely that any formula would also need modifying itself in any case.
If you're expanding data by adding new records/rows then a dynamic formula that covers the data range will adjust automatically and the PT would then be based on this dynamic range name.

I'm struggling to really understand why you appear fixated on a formula.

Originally Posted by bjnockle

Richard Buttrey: Using PT would mean the user will have to manually refresh or use VBA code to auto trigger refresh. Thanks

Not necessarily. Personally I always use a Sheet Activate macro event on the Pivot Table sheet which automatically refreshes the pivot table and I can forget all about needing to do it manually.