Forum Discussion
Help creating an excel report
Hi all, I would appreciate someone being able to give me some guidance as to build the report how best I need.
In the attached spreadsheet I have a set of data that I want to be able to filter based on multiple criteria and the results to then be extracted to a new sheet within the same workbook.
I have created a version where I was using connected dropdown boxes however, I ran into some trouble when I wanted to select multiple criteria within a single column. Additionally if I wanted to reset the data so I could see all results I was unable to create a function to do that.
In the spreadsheet I have the below column names. Within each column there are multiple items. Eg, for supplier there is supplier 1, supplier 2, supplier 3 etc, in “State” there is NSW, ACT, VIC etc.
As an example, I want to be able to create a report where I can choose a combination of data, say. For “state” = choose VIC, For supplier choose “Test ABC”, For lender Name choose “Finance 2” & for equipment type chose “Telco”.
I would then like for the output of the report to show and then if required extract the data to another sheet.
I would also like to be able to multiple criteria from each column. For example when I chose the equipment type, I want to be able to choose “telco & solar”.
I would love some guidance on the best way to build such a report.
Pmt Plan Type |
ApplicantName |
State |
Supplier Name |
CP Brand |
Amount |
FundedDate |
Sched term end date |
Lender Name |
Equipment Type |
ApplicantEmail |
4 Replies
robwill100 You could use Advanced Filter for that. See attached file and read more about the feature in the link below.
https://www.howtogeek.com/848055/how-to-use-advanced-filter-in-excel/
- Hi, thanks for the quick response. I can see the output but can't see any filter formulas, were there meant to be formulas in the sheet?
robwill100 The link explains how it all works. There are no filter formulas, though. The filter criteria are inserted in the section that I inserted above the raw data. A bit peculiar way of entering them, but that's how it works.
Change the criteria, press Advanced Filter again and Excel will have remembered the data and criteria ranges. Don't forget to select "Copy to another location" again.
If you insist on using an all in one formula, you could try this:
=VSTACK(FILTER(All_Funded,(All_Funded[State]="VIC")*(All_Funded[Supplier Name]="Test ABC")*(All_Funded[Equipment Type]="Telco")),FILTER(All_Funded,(All_Funded[State]="VIC")*(All_Funded[Supplier Name]="Test ABC")*(All_Funded[Equipment Type]="Solar")))It's a monster and can probably be improved. but I can't think of a way right now.
