locked
Is Power Query the best way to build tables in other worksheets based off of a master table? RRS feed

  • Question

  • I have a large table with team financial data. I need to create separate worksheets for each team based on their team name. The financial data is in flux so if I update the data in the master sheet, I need the data to update in each of the teams' worksheets. I dont mind learning, but would like to use the right "toolset" in Excel. I have Office 365 but needs to work with Excel 2016 users.

    My thought is to create one sheet and then replicate it 10 times, each time having Excel build the team table based on the Team name in in A1. There are only about 15 teams and entering the individual team name in A1 for each sheet seems straightforward enough way to individualize each page.

    1. Unique team name like EntW, EntE, EntS, EntN...
    2. Based on the team name pull in the team members name and the financial data

    Seems really straightforward but I am not an excel guru.

    Thanks for any pointers.


    • Edited by Dutch der Friday, December 27, 2019 6:48 PM
    Friday, December 27, 2019 6:48 PM

Answers

  • Dutch der,

    Just to be direct, I think Power Query / Power Pivot is the correct "toolset" as you put it.

    Is the data private between teams?  Said another way, would it be a problem if all teams had access to the other teams data?  If the answer is "no" then build one table in Power Query, set up a Pivot Table report with team name as a filter.  Each team can select their name from the filter pick list to see their data.

    If it has to be private, in Power Query you could "hard code" the team name so the data is filtered to only the team's data. You would then need to password protect the workbook.  In this case you would also need one file for each team, or you could get clever and pull the team name from the file name.  No one would know, but you would just have to make sure the team name was in the filename.  As an alternate approach, you may be able to get fancy with some RLS (Record Level Security), and get the username from the computer accessing the data and then filter the data based on that username. (Not sure how one would go about doing this as I have never done it yet.)

    Hope this helps...


    John Thomas


    Thursday, January 2, 2020 8:10 PM

All replies

  • Power Query is more for getting your data aggregated and setup than displaying it to users. If all your data is already in a table, you probably can just use filtered pivot tables or formulas with sumifs to setup your tabs to have data displayed for each of the teams.
    Monday, December 30, 2019 10:06 PM
  • Dutch der,

    Just to be direct, I think Power Query / Power Pivot is the correct "toolset" as you put it.

    Is the data private between teams?  Said another way, would it be a problem if all teams had access to the other teams data?  If the answer is "no" then build one table in Power Query, set up a Pivot Table report with team name as a filter.  Each team can select their name from the filter pick list to see their data.

    If it has to be private, in Power Query you could "hard code" the team name so the data is filtered to only the team's data. You would then need to password protect the workbook.  In this case you would also need one file for each team, or you could get clever and pull the team name from the file name.  No one would know, but you would just have to make sure the team name was in the filename.  As an alternate approach, you may be able to get fancy with some RLS (Record Level Security), and get the username from the computer accessing the data and then filter the data based on that username. (Not sure how one would go about doing this as I have never done it yet.)

    Hope this helps...


    John Thomas


    Thursday, January 2, 2020 8:10 PM