I am responsible for tracking sales visits of 22 sales reps in 3 different areas (central, east, west.) I made an excel spreadsheet that I email out to each of them that they fill out every day documenting their visits. At the end of the week they will individually email me their completed sheet. All the sheets are identical, just with different values entered into the cells by the reps. I need to be able to put all the individual rep's sheets together into one excel file, containing a master sheet that is identical to the rep's sheets, but with two added drop-down boxes that give you the option to select the area and rep's name, and automatically populate with the information pulled off their sheet. I need to have this function for each area (central, east, west.)
So for example, for the East area manager, I would send him an excel file at the end of each week that only has one sheet in it, but has a drop down box where he would select East and then another drop-down box where he could select each of his rep's names and the current sheet would populate with the information pulled off their sheets. The idea that I have that I can't seem to figure out how to execute is to name each spreadsheet with the rep's last name (because this will not change unless someone is promoted or a new hire), and then have a reference (use indirect somehow maybe?) where once the East area manager clicks East and the rep's name, John Doe, the information from the sheet named Doe will automatically populate that sheet.
I know how to make drop-down boxes, but I do not know how to make a sheet reference inside a drop-down box for each different selection. (Note: I am not trying to reference only a few specific cells; I need to reference the entire worksheet, with the exception of the two cells that have the drop-down boxes to select the area and rep name on the master sheet.) I figured I could open up each sheet from each rep in the East (or other area) and combine their individual sheets in one workbook, and once I figure out how to do the drop-down box to select each rep, I could hide the rep's sheets, so when I email the East area manager all he sees is one sheet with the option to select all the different rep's sheets in the drop-down box.
So once I am able to achieve that degree of functionality, there are 2 other things I need to be able to do. In addition to being able to select each individual rep's sheets, I need the East manager to be able to see totals out of 3 cells combined for his whole team. So let's just say Product A, Product B, and Product C are automatically summed on each rep's sheet. I need the East area manager to be able to select just East in the drop down box, and see totals of those 3 items for all his rep's combined. Then, I need the drop down box to have an option to select all areas totals, so that the manager over all three of the areas (the East, West, and Central areas) can not only see the product totals for each rep or each area altogether, but also for all three areas combined.
And lastly, I will be receiving the individual excel sheets for each rep weekly, but I also need to be able to track the product totals for each rep, area, and areas combined for each month and each quarter. The idea is not to have more than 2 tabs any manager at any level has to navigate between (this is the reason I'm thinking maybe I can hide the sheets?), and honestly, one tab would be preferable if possible. The other users have even less experience in excel than I do so I need to keep things as simple as possible, and they will also normally be on tablets, so I want to make this as user-friendly as possible.
Now, other than all that, my one concern is if my train of thought on naming the sheets by rep's last name is a bad idea. The reps don't change very often, I would say maybe there is a change or we get someone new six times a year, and that would be in a very turbulent year if there was a lot of role changes, and normally there is not in the company I work for. But I realize if I'm referencing sheet names, if the sheet name changes this could potentially be a problem. I will attach a scrubbed example file shortly. My apologies for my inability at brevity, and thanks in advance for any suggestions.
Bookmarks