Filtering w/o Using Data Filter

[hazybuck]

I am developing a 'Topline Report' for senior management . . . and they want filters on the report. Normally, I'd use a pivot table for what they're requesting, but I cannot in this instance.

The filters, four in all, must:
1. Be in drop down boxes (Bottler, MU, Region, and DC)
2. Impact the data in the 'Topline Report' - for instance . . .

When you open the report:
- The Topline Report reveals ALL orders.
- When the filters are utlilized, only the order quantities for THOSE locations are revealed - based on what's selected in the filters (Bottler / MU / Region / DC), so the order numbers would decrease in the report below.

The only thing I can think of is a 'mile long' IF statement that considers EVERY possibility, and there MUST be a more elegant solve.

Thoughts?!?

 

[Somendra Misra]

Hi,

The quick way I can think of using Advanced filter with a macro. Is this OK.

Regards,

 

[hazybuck]

Somendra, could you be a bit more specific? I believe your idea has intriguing possibilities!

Understand that this report is going out to 15 end users, so I must take EVERY possibility into consideration - every one!

Thank you for your response.

KB

 

[Somendra Misra]

The design will be like this:

1. Link all the four selection to a calculation sheet
2. Create a macro of worksheet change event with these four selections and use them as criteria for running the advanced filter to extract data based on the selection made by the user on the calculation sheet
3. Display the required data from the extracted data on the report sheet.

Regards,