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.

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

<tbody>
</tbody>

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.

2017 Sales Representative Quote Activity Report
Sales Rep:	Fox	Territories:	Indiana, Ohio, Michigan	Month:	November
_	_	_	_	_	_	_
Activity	Date	Quote #	Project Name	Customer Name	Unit	Quote Price
Quote	11/8/16	16.2114a	Project 2	def	24	$56,000
Sale	11/12/16	16.2118a	Project 3	jkl	27	$58,000

<tbody>
</tbody>

 

[HackSlash]

This can be done with filters.

1. Click a header that you want to fileter by, like the date on A2.
2. Click DATA > Filter. (Now you should see little drop down arrows on each column.)
3. Click the arrow on Quote Date and select "Date Filters"
4. Select a filter that you want like "Last Month"
5. Now click the arrow on the Name Field
6. Deselect "(Select All)"
7. Click the checkmark next to a name

Now you have accomplished your goal with zero programming using the features built in to Excel!

 

[SpillerBD]

To get the closest without any macro, you'll be using PivotTables. For the Date Selection you can use Group by Year and Month or use the Time Line feature set to show months. (You still have the regular drop-down filter options too.)

There are some array formula methods I believe Mike Girvin has demonstrated that could get a closer match to what you have mapped, but I don't like the way you would have to fill down an undetermined amount of rows as it can create issues for printing or other statistical analysis.

 

[andrewbhilty]

This can be done with filters.

1. Click a header that you want to fileter by, like the date on A2.
2. Click DATA > Filter. (Now you should see little drop down arrows on each column.)
3. Click the arrow on Quote Date and select "Date Filters"
4. Select a filter that you want like "Last Month"
5. Now click the arrow on the Name Field
6. Deselect "(Select All)"
7. Click the checkmark next to a name

Now you have accomplished your goal with zero programming using the features built in to Excel!

The Master Quote Log has roughly 25 columns of information. We only report 8 of those columns, but the columns reported are not right next to each other. The Report is in a different sheet of the same workbook. Filtering will not work in this case.

I appreciate the input though.

 

[andrewbhilty]

To get the closest without any macro, you'll be using PivotTables. For the Date Selection you can use Group by Year and Month or use the Time Line feature set to show months. (You still have the regular drop-down filter options too.)

There are some array formula methods I believe Mike Girvin has demonstrated that could get a closer match to what you have mapped, but I don't like the way you would have to fill down an undetermined amount of rows as it can create issues for printing or other statistical analysis.

I'm not familiar with Pivot Tables... If that's what it takes, I'll definitely look into it.

As for the undetermined amount of rows, there are typically between 20-40 quotes per month, and always fit on a single page when printing. The Master Quote Log has a determined amount of rows, although the amount is somewhat large (600). I'll look up Mike Girvin and see if I can apply anything.

I feel like there has to be a way to do this with an =ifs() statement and vlookup somehow. I could be wrong, just seems like something it could do.

 

[HackSlash]

The Master Quote Log has roughly 25 columns of information. We only report 8 of those columns, but the columns reported are not right next to each other. The Report is in a different sheet of the same workbook. Filtering will not work in this case.

This second sheet can just be pointers to the first sheet. That way you can put any data selection in any order and then filter it.

 

[andrewbhilty]

This second sheet can just be pointers to the first sheet. That way you can put any data selection in any order and then filter it.

Pointers? I think this might work if pointers do what I assume they might?

 

[HackSlash]

Yeah, type equals sign in a cell. Now click a different cell to make a pointer. The values are linked uni-directionally. The reference is relative so Excel can auto-fill and intelligently hand copy\paste. Just make them by column in the order you want. It's super quick.

More information about the fill handle:
https://support.office.com/en-us/ar...et-cells-74e31bdd-d993-45da-aa82-35a236c5b5db

 

[andrewbhilty]

Yeah, type equals sign in a cell. Now click a different cell to make a pointer. The values are linked uni-directionally. The reference is relative so Excel can auto-fill and intelligently hand copy\paste. Just make them by column in the order you want. It's super quick.

More information about the fill handle:
https://support.office.com/en-us/ar...et-cells-74e31bdd-d993-45da-aa82-35a236c5b5db

Well, never had heard that called pointers, but that will not exactly work. The Report page that is set up has a specified number of rows for the items to fill in. Then below that specified number of rows is a summary of information. If I were to use pointers for all of the information on the Master Quote Log and then filter, I would have to reformat the Report Page to print for each employee, for each month, which doesn't save me much time.

As I said, no employee does more than 30-35 quotes a month on our busiest months, so the format of the page will work as set-up, if we could figure this out. The format of the Report page is set with the specified number of rows and columns in order to print on a single page and provide all the information we need to review.

See below Full Report Page:

2017 Sales Representative Quote Activity Report
Sales Rep:	Fox	Territories:	Indiana, Ohio, Michigan	Month:	November
Quote Status	Date	Quote #	Project Name	Customer Name	Units	Quote Price
Row 1
Row 2
...
...
Row 39
Row 40
# of Quotes in November		$ Quoted in November		# Units in November		$ / Unit in November
0		$0		0.0		$0
YTD Total		YTD $ Total		YTD Total Units		YTD Average $ / Unit
# of Quotes		$ Quoted
0		$0		0.0		$0

<tbody>
</tbody>

I tried messing with the PivotTable feature, and either I don't know how to use it very well (which is likely), or it's not quite what I need. Perhaps there is no way to do this without Macros, but I really expected vlookup to work.

 

[SpillerBD]

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

Pivot Tables. Start at this playlist https://www.youtube.com/playlist?list=PLDF4390866B480CD2
While Mike has excellent in Depth videos, the book to use is Bill Jelen's as well as Bill's full Excel book Excel 213 in Depth
Versions for 2016 should be near publishing. Available through Barnes&Noble and the MicrosoftPress Store too.

If you are not familiar with PivotTables yet, you may not be ready for Get&Transform (formerly Power Query) but that can also provide a solution too. Mike has a series on those techniques that you may not want to try just yet , but I do suggest that you watch some info on the topic for the future.

This video Excel Magic trick 1337 is neat since it shows all three methods (though in just simple terms.)