Without using Macros - Generate a Monthly Report Based on Month and Employee

[andrewbhilty]

I have been trying to fix a clunky time wasting report process we have so that Excel does all the time wasting work for me, and all I have to do is select an employee and a month and press print. Here's what I'm working with so far:

There is a master quote log that multiple employees have access and add information to. In that sheet, we keep track of the person who input the quote, the date the quote was entered, and a variety of information that varies from quote to quote.

On a separate sheet in the same workbook, I'm trying to set-up what we report once monthly. What I would like the spreadsheet to do, without using Macros, is allow the user to select an employee from a drop down and the month from a drop down. It will then fill in the table with the seclect columns that we report (I can edit the column headers if they have to match exactly from sheet to sheet).

Is there anyway to do this without using Macros with an If statement and vlookup? If been playing with it for a while with no luck. Trying to say something like ifs Sales Rep = "Fox, Month = "November", Report "Columns A, B, C, D, F, H, L,...

Hopefully that will make sense when you see the spreadsheet examples below:

Here are examples of the two sheets - first one is the Master Quote Log. Second one is the Report.

[TABLE="width: 1099"]
<tbody>[TR]
[TD="colspan: 14, align: center"]Master Quote Log[/TD]
[/TR]
[TR]
[TD]Quote Status[/TD]
[TD]Quote Date[/TD]
[TD]Quote #[/TD]
[TD="colspan: 3"]Project Name[/TD]
[TD="colspan: 3"]Customer Name[/TD]
[TD]Sales Rep[/TD]
[TD]Quoted Price ($)[/TD]
[TD]Sq. Ft.[/TD]
[TD]Unit[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]SOLD[/TD]
[TD]01/12/16[/TD]
[TD]16.1424b[/TD]
[TD="colspan: 3"]Project 1[/TD]
[TD="colspan: 3"]abc[/TD]
[TD]Doe[/TD]
[TD]$1,265,456[/TD]
[TD]128,255[/TD]
[TD]628[/TD]
[TD]322[/TD]
[/TR]
[TR]
[TD]QUOTED[/TD]
[TD]01/16/16[/TD]
[TD]16.2114a[/TD]
[TD="colspan: 3"]Project 2[/TD]
[TD="colspan: 3"]def[/TD]
[TD]Fox[/TD]
[TD]$56,000[/TD]
[TD]5,500[/TD]
[TD]24[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]DEAD[/TD]
[TD]02/16/16[/TD]
[TD]16.1016a[/TD]
[TD="colspan: 3"]Project 3[/TD]
[TD="colspan: 3"]ghi[/TD]
[TD]Deere[/TD]
[TD]$58,000[/TD]
[TD]5,200[/TD]
[TD]27[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]DEAD[/TD]
[TD]04/05/16[/TD]
[TD]16.2118a[/TD]
[TD="colspan: 3"]Project 4[/TD]
[TD="colspan: 3"]jkl[/TD]
[TD]Fox[/TD]
[TD]$110,000[/TD]
[TD]15,000[/TD]
[TD]54[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]



Report: .Only Items edited are Sales Rep (drop down list) and Month (drop down list). I want everything else to pull from the Master Quote Log.
[TABLE="width: 766"]
<tbody>[TR]
[TD="colspan: 11, align: center"]2017 Sales Representative Quote Activity Report[/TD]
[/TR]
[TR]
[TD]Sales Rep:[/TD]
[TD="colspan: 3"]Fox[/TD]
[TD="colspan: 2"]Territories:[/TD]
[TD="colspan: 3"]Indiana, Ohio, Michigan[/TD]
[TD]Month:[/TD]
[TD]November[/TD]
[/TR]
[TR]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="colspan: 3, align: center"]_[/TD]
[TD="colspan: 3, align: center"]_[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]
[/TR]
[TR]
[TD]Activity[/TD]
[TD]Date[/TD]
[TD]Quote #[/TD]
[TD="colspan: 3"]Project Name[/TD]
[TD="colspan: 3"]Customer Name[/TD]
[TD]Unit[/TD]
[TD]Quote Price[/TD]
[/TR]
[TR]
[TD]Quote[/TD]
[TD]11/8/16[/TD]
[TD]16.2114a[/TD]
[TD="colspan: 3"]Project 2[/TD]
[TD="colspan: 3"]def[/TD]
[TD]24[/TD]
[TD]$56,000[/TD]
[/TR]
[TR]
[TD]Sale[/TD]
[TD]11/12/16[/TD]
[TD]16.2118a[/TD]
[TD="colspan: 3"]Project 3[/TD]
[TD="colspan: 3"]jkl[/TD]
[TD]27[/TD]
[TD]$58,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

This sounds like AdvancedFilter would be useful for the filtering.
If you have your data base on one sheet, your Criteria Range somewhere and your Report Sheet a third place you could use the CopyToOther location option.

The unwanted columns of the Report Sheet could be pre-hidden.

 

[andrewbhilty]

This should help see a formula method for getting your results https://www.youtube.com/watch?v=9jmN...A7644FE57C97F4
What I don't like is the need to build/rebuild your list for the drop-down filters.

The formula he uses does exactly what I need it to do. This was extremely helpful. Gonna take a little while to play with it to make sure it works without missing anything, but I really think that's what will make it work!

I really appreciate it.