+ Reply to Thread
Results 1 to 14 of 14

Best Solution to create a Master Spreadsheet

  1. #1
    Registered User
    Join Date
    07-15-2019
    Location
    Leeds
    MS-Off Ver
    2016
    Posts
    4

    Lightbulb Best Solution to create a Master Spreadsheet

    Hi All,

    I've been working on a project recently to create a variety of client spreadsheets for around 10-13 consultants.

    I've created these spreadsheets and I'm pretty happy with how they look.

    I now want to be able to link all these spreadsheets to one Master workbook, which is accessible by myself and a few other senior managers. Accessibility is fine, I can sort that. Infomation taken from our consultants sheets would be client names and commission tracked.

    My question is, how would I go about creating a link from all of our consultantís sheets to our master sheet to provide live/hourly updates on data? I've never used a Macro before, but I am keen to learn if this would be possible to create a solution!

    Any advice is appreciated!

    Kind Regards,

    Duncan

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Best Solution to create a Master Spreadsheet

    You can use Power Query to combine data from multiple workbooks, really easily.

    Something like:

    Please Login or Register  to view this content.
    You'll probably want to tweak to suit your purposes - feel free to upload a sample workbook, for more specific help.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,111

    Re: Best Solution to create a Master Spreadsheet

    A shared workbook is not something I'd nomally recommend but if your 'spreadsheets' are single sheet workbooks and each one is only updated by a single individual then having all the sheets in a Shared master workbook might be an option

    That master workbook could contain a simple macro to create a single table from all the indvidual sheets' data if necessary.
    Last edited by Richard Buttrey; 07-15-2019 at 08:12 AM.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Best Solution to create a Master Spreadsheet

    Olly - I am not sure PowerQuery will work here. The way I read it, the source workbooks will be queried once every hour, and will most likely be open at that time. PowerQuery needs them to be closed, doesn't it?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Best Solution to create a Master Spreadsheet

    No, Ali, you can use Power Query to get data from open workbooks.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Best Solution to create a Master Spreadsheet

    How come, then, when I try to do so, it tells me I can't because the source workbook is open? What's the trick?

    EDIT: Only if it's relevant to this thread, of course!!! I am not hijacking.
    Last edited by AliGW; 07-15-2019 at 09:00 AM.

  7. #7
    Registered User
    Join Date
    07-15-2019
    Location
    Leeds
    MS-Off Ver
    2016
    Posts
    4

    Lightbulb Re: Best Solution to create a Master Spreadsheet

    Hi Guys!

    Thank you for your suggestions, a bit more light on this, I've attached an example of what the Consultants will all fill out themselves with their clients and info. They have different monthly tabs for each consultant.

    What would be ideal would be to get the information as regularly as possible onto a master spreadsheet for analysis. I have attached a simple example of what I have drafted thus far for the Master Spreadsheet!

    I really appreciate your help guys!

    Duncan
    Attached Files Attached Files
    Last edited by Chapmadr01; 07-15-2019 at 09:00 AM.

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Best Solution to create a Master Spreadsheet

    Your samples are only partially completed, which doesn't help us definitively understand your requirement.

    Can you describe how data maps from each consultant file, into the master file? The column headers don't match.
    Where are you sourcing consultant names?
    The "Monthly Adjustments" requirement isn't clear at all.

  9. #9
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Best Solution to create a Master Spreadsheet

    Quote Originally Posted by AliGW View Post
    How come, then, when I try to do so, it tells me I can't because the source workbook is open? What's the trick?

    EDIT: Only if it's relevant to this thread, of course!!! I am not hijacking.
    Might be worth you starting another thread, with your code, Ali, and I'll have a look...

  10. #10
    Registered User
    Join Date
    07-15-2019
    Location
    Leeds
    MS-Off Ver
    2016
    Posts
    4

    Re: Best Solution to create a Master Spreadsheet

    Hi Olly,

    I would VLOOKUP from the the individual consultant spreadsheets (new collumn with consultant name in for that sheet) if the the client name was able to appear on the master spreadsheet, the VLOOKUP would pick up the consultant name right? EG clients moving from consultant to consultant, the name of the consultant would change accordingly. Would this work in your mind?

    Sorry I left the monthly adjustments section in, I can sort that section (probably manually as these are rare).

    The main requirement is getting Client name, expected commission and actual commission into the master sheet for each month.

    EG. Tim, Duncan, Dave all complete their client spreadsheets for January, Master spreadsheet picks up all the client names for that month and product type, along with the inputted data from expected commission and actual commission recieved.

    Appolgies for the long winded approach, I'm new to this! Your help is really appricated thank you!

    Kind Regards,

    Duncan

  11. #11
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Best Solution to create a Master Spreadsheet

    Which source columns contain Expected Commission and Actual Commission?
    The source files are split into multiple tables (rows). Which section(s) (row numbers) do you want to include?

    If you intend to maintain a map between clients and consultants, how do you intend to manage clients moving between consultants? What happens when Client A moved from Consultant Amanda M to Consultant Anna B? Alternatively, if your filenames are consistent, you could get the Consultant name from the source file name...

  12. #12
    Registered User
    Join Date
    07-15-2019
    Location
    Leeds
    MS-Off Ver
    2016
    Posts
    4

    Re: Best Solution to create a Master Spreadsheet

    Ahhh file name is consistant for each consultant, so the source file name method would work a treat! (Great idea!)

    Source columns are K and O on consultant tracker, the formulas in the background produce a figure in those columns, this would be the were the data would be pulled from. If possible, for both Fee (top section) and Commission clients (2nd section).

    So for Fee sections I'd say include Rows 3 to 6 (I can expand the fee section on each tab to account for this, as they are only set at 5 currently). Then for the commission section, this would be row 11 to 45, I can edit any of the sheets that arew currently not at this number of rows to match.

  13. #13
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Best Solution to create a Master Spreadsheet

    You can combine all the consultant data in a folder, using this query:

    Combined Consultant Data:
    Please Login or Register  to view this content.
    Close and load to Data Model, then create a pivot table to display results. Data > Refresh All to update.

    See attached workbook for a worked example.
    Attached Files Attached Files

  14. #14
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Best Solution to create a Master Spreadsheet

    Note, I included your "Comission" typo in the query, for filtering client types. If you're changing this on the source files, be sure to change the filter in the query, too.

+ 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. Replies: 1
    Last Post: 02-26-2014, 08:54 PM
  2. Creating a master spreadsheet based on various spreadsheet in Sharepoint
    By lmoura in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-06-2013, 05:09 PM
  3. Macro solution needed
    By Cobbhill in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2012, 10:38 AM
  4. Replies: 8
    Last Post: 02-28-2012, 12:04 PM
  5. Replies: 2
    Last Post: 08-12-2011, 10:08 AM
  6. Replies: 4
    Last Post: 07-11-2011, 07:27 PM
  7. efficient solution to using a master file?
    By adds007 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-18-2011, 09:03 AM
  8. How can I create a master spreadsheet from different workbooks
    By Dawn Williams in forum Excel General
    Replies: 1
    Last Post: 05-03-2006, 07:15 AM

Tags for this Thread

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