+ Reply to Thread
Results 1 to 6 of 6

How to create a copy of a master file, so each sales rep has their own sheet.

  1. #1
    Forum Contributor
    Join Date
    01-07-2012
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    157

    How to create a copy of a master file, so each sales rep has their own sheet.

    Hello,

    I have a Master File with a lot of information in it that gets updated by my sales reps on a weekly basis. What I would like to do is create copies of this file so that each sales rep has one sheet/tab at the bottom. (Basically the exact same sheet x # of times and then just filter each one to a different rep).

    All of the data will be always entered into the Master File, but for me to review a specific sales rep I would like to be able to have a sheet just for them.

    Is there a way to copy the format of a spreadsheet but have the entire sheet reference to the Master sheet. Or is this something pivot table situation?

    I know of a few ways to make this work but I don't think they are the best ways to do it. Any suggestions?
    Last edited by PaddyP; 02-01-2021 at 09:46 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2506 Win 11
    Posts
    24,823

    Re: How to create a copy of a master file, so each sales rep has their own sheet.

    Could be done with a parameter query in Power Query. If you are able to provide a sample workbook (see yellow banner at top of this page), I will demonstrate.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    01-07-2012
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    157

    Re: How to create a copy of a master file, so each sales rep has their own sheet.

    Here is an example of what I was thinking but I don't know if this is the best way to do it. My sheet is much bigger and has conditional formatting it so I am not sure if it makes a difference or not.

    Thank you for taking the time to look into it!
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2506 Win 11
    Posts
    24,823

    Re: How to create a copy of a master file, so each sales rep has their own sheet.

    With Power Query, I created a parameter (drop down for salesman). Then added the Data table to the PQ Editor and set the filter for the salesman to the parameter. To make it work, select your salesman in the drop down and then click on the refresh button.

    Attached is the file for your review.

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    01-07-2012
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    157

    Re: How to create a copy of a master file, so each sales rep has their own sheet.

    Wow this is so cool! I will definitely look into learning more about this awesome feature. Thank you for taking the time to introduce me!

    I'll probably come crawling back with questions but let me go see what I can do on my own!

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2506 Win 11
    Posts
    24,823

    Re: How to create a copy of a master file, so each sales rep has their own sheet.

    A good primer for this is "M is for (Data) Monkey" by Ken Puls and Miguel Escobar available at Amazon.

    Thanks for the Rep

+ 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. Formula or data copy to help create a Master Sheet
    By EleGuy87 in forum Excel General
    Replies: 5
    Last Post: 04-12-2019, 07:52 PM
  2. Need to create new sheet based master template and copy some parts over?
    By batmaniac in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-12-2016, 03:58 AM
  3. Create a copy of hidden master sheet and vba code
    By kdr2903 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2015, 04:23 PM
  4. [SOLVED] Using =G8-SUMIF('MASTER SALES'!C:C,A8,'MASTER SALES'!J:J) but I forgot to include # sold
    By Just Splurge in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-30-2014, 02:33 AM
  5. [SOLVED] Trying to create a new workbook for each sheet in a master file
    By smls in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-16-2013, 02:31 PM
  6. [SOLVED] Copy data and file name into master sheet
    By cokillerliu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2013, 12:07 PM
  7. Macro to Look for Sheet, create if none, sort items from master & copy to new sheet.
    By ali.whitaker in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-25-2012, 12:58 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