+ Reply to Thread
Results 1 to 9 of 9

Need a drop-down box that works as a sheet reference and populates current sheet

  1. #1
    Registered User
    Join Date
    01-14-2016
    Location
    Dallas, TX
    MS-Off Ver
    Office 2013
    Posts
    4

    Need a drop-down box that works as a sheet reference and populates current sheet

    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.
    Attached Files Attached Files
    Last edited by lizzy2007; 01-14-2016 at 05:28 PM. Reason: attach example file

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Need a drop-down box that works as a sheet reference and populates current sheet

    If you take a look at your post, you will (hopefully) agree that it's not easy to read!!! can you a) edit your post to break it up into paragraphs and b) attach a sample worksheet showing what you want to have done.
    Glenn



  3. #3
    Registered User
    Join Date
    01-14-2016
    Location
    Dallas, TX
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: Need a drop-down box that works as a sheet reference and populates current sheet

    My apologies Glen. I broke it up, and I will attach a sample worksheet shortly.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Need a drop-down box that works as a sheet reference and populates current sheet

    I wouldn't use the rep and manager names for file titles. Use something that is very unlikely to change or create a designation for each. For the purposes of your Excel workbook, you could create and ID for each location and assign a number for the reps and managers. Let's say the ID for a location is 100. The manager could be 1 so a file for the manager would be 100-1. The reps under the manager would be numbered with values other than 1. You could keep track of what number is assigned to what rep with a VLOOKUP table.

    If you use names, every change of personnel will be a real headache changing all the references for that individual. If you used ID's all you have to do is to assign the new name to the ID.

    There are add ins available to merge workbooks. Here is a reference to a Microsoft MVP who has such an add in
    http://www.rondebruin.nl/win/addins/rdbmerge.htm
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    01-14-2016
    Location
    Dallas, TX
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: Need a drop-down box that works as a sheet reference and populates current sheet

    Thank you for the suggestions I definitely appreciate it. I have added a file with sample data too, just to give an idea of what I'm trying to do. The sheet named "master" is the one where I want the drop down box (in orange) to pull the all the sheet data from the sheet named "sales_rep_sheet".

    After I figure out how to make that happen, I want to figure out how to make it so you can select an area (Central, East, West) from the drop down box without selecting a specific sales rep, and it will pull the combined "Grand Totals" (also in orange) from all the sales reps for that area.

    I also want to have an option in the drop down box to pull the totals for all 3 areas combined. I am going to delve into the link you sent me tomorrow as I am about to leave work for today, but once again, all insight is appreciated!

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Need a drop-down box that works as a sheet reference and populates current sheet

    Enter this in Master!A5 and fill across and down. This is an ARRAY FORMULA so enter with Ctrl+Shift+Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This formula takes the entries from both A2 and B2 to return records from the Sales Rep Sheet
    Attached Files Attached Files

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Need a drop-down box that works as a sheet reference and populates current sheet

    To get sheet references in drop downs, enter the worksheet names in a list and assign that list to a drop down in Data Validation. You can then in another cell on the worksheet with the drop down address that worksheet with a formula like this. A2 has the worksheet name.

    =INDIRECT("'"&A2&"'!CELL OR RANGE")

    I'm not entirely clear on what you want the managers to get but you could have a separate worksheet for each manager that simply lists all the employees in the area and make use of the Auto Filter or even Pivot tables to create reports for the manager's area.
    Last edited by newdoverman; 01-14-2016 at 07:44 PM.

  8. #8
    Registered User
    Join Date
    01-14-2016
    Location
    Dallas, TX
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: Need a drop-down box that works as a sheet reference and populates current sheet

    My apologies for the delay in responding. Thank you so much for the above link to the add-in that allows you to merge workbooks. I finally had a chance to use it, and it will definitely help me on this project. I have been googling for literally over a month, trying to find something that would help me, and this is it. I wouldn't have found it on my own. I simply cannot thank you enough.

    I also have had time to look at your suggestions above, and the sample file, and all of this is exactly what I needed to get started. I appreciate you helping me immensely. Thank you for taking your time to teach someone much less experienced. Once I have worked with the data and the tools above more, I am going to begin learning pivot tables. You are right. I will be able to do exactly what I need to with the add-in, suggestions/formulas above, and through pivot tables.
    Thank you!!!
    Last edited by lizzy2007; 02-02-2016 at 12:33 PM.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Need a drop-down box that works as a sheet reference and populates current sheet

    Thank you for the feedback and good luck with your project.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Reference cell on another worksheet based on value on current sheet
    By clocke in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2014, 12:24 PM
  2. [SOLVED] Hyperlink to always reference current sheet
    By Yoshi64 in forum Excel General
    Replies: 1
    Last Post: 10-15-2014, 02:46 AM
  3. [SOLVED] code populates list boxes in active sheet, but does not populate boxes in subsequent sheet
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-13-2014, 10:26 AM
  4. Replies: 8
    Last Post: 07-26-2013, 06:29 PM
  5. [SOLVED] Reference of Previous sheet in current sheet cell.
    By ranjeet.bhagat57 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-25-2012, 11:37 AM
  6. Changing reference sheet based on current quarter?
    By GarnetBlack32 in forum Excel General
    Replies: 2
    Last Post: 02-05-2012, 05:19 PM
  7. Replies: 2
    Last Post: 03-24-2011, 11:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1