Creating A Report with 5 drop down menus

**SakebR** · 01-26-2011, 03:16 PM

Hello All

I was wondering what would be the best way to create a report using drop down lists

I have created a report which has two drop down menus, which currently relate to source of business and time period. At the moment, as the conditions are changed, the data within the related table and graphs change accordingly. The back-end data is currently set up in pivot tables, and I have used if functions with hlookups to call the data

eg. =IF($E$3 = "Web",HLOOKUP($F$3,'C_H_NB_Vol Pivot Tables'!$66:$80,Lookup!$L9,FALSE),IF($E$3 = "All",HLOOKUP($F$3,'C_H_NB_Vol Pivot Tables'!$6:$20,Lookup!$L9,FALSE),IF($E$3 = "Phone",HLOOKUP($F$3,'C_H_NB_Vol Pivot Tables'!$46:$60,Lookup!$L9,FALSE),IF($E$3 = "Aggregator",HLOOKUP($F$3,'C_H_NB_Vol Pivot Tables'!$26:$40,Lookup!$L9,FALSE)))))

However, to develop this report further, I need to add a further a 3 drop down options: product (motor, home, etc), brand, type (New Business, Inforce)

Help? What functions would be best for this?? Can I have different formulas pre written into cells on a 'lookup' worksheet and then within my formula, reference different batches of code to use where necessary?

Or would i need to use VBA coding and use buttons as opposed to drop down lists?

Please help...

**royUK** · 01-26-2011, 03:23 PM

Have you looked at PivotTables?

**SakebR** · 01-26-2011, 03:35 PM

Yeah, the back end data is created in pivot tables, where the formula references different tables depending on the options selected

**royUK** · 01-27-2011, 03:52 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**SakebR** · 01-27-2011, 11:36 AM

In order to support my query, I have attached the workbook I am working on

Essentially, as the options across the top change, the data within the table and the graphs should change accordingly

At the moment, only two of the drop downs work, Time Period and Channel

Please help

If I need to re-write the formulae, any suggestions?