andrewbhilty
New Member
- Joined
- Nov 23, 2016
- Messages
- 8
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.
<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.
<tbody>
</tbody>
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>
