Log In
Register
Home
Power Query
Power Pivot
Power BI
Dashboards
November 23, 2023
Offline
Hello!
First time poster. I have a workbook with a Master tab with lots of Conditional formatting. that I’d like to break out to multiple separate tabs and when either the master or one of the tabs is updated the conditional formatting is updated on both sides. The master sheet contains about 12 sites and I plan to break it out on 12 separate tabs. If I need to break each one out first and update to the master I’m fine with that as well.
Is this possible and if so, what’s the general way to do it? I’ve been search online and haven’t been able to figure out a way to do it.
Thanks for your time and consideration.
Robert
See Answer
Moderators
January 31, 2022
Offline
Possible? Yes.
But why? Without having seen your file I would say that it's best to keep all in one structured table and then create summaries per site from that master table.
If you could upload a file that shows what you have and how you are applying conditional formatting that would help. Just remove anything that is private and confidential. Replace it with some fake data.
Power Query
Power Pivot
Power BI
Dashboards
November 23, 2023
Offline
Riny,
The reason I'm trying to push it to separate tabs is because I'm planning to push this to sharepoint and have individuals who manage the site(s) go in and update the data where required and I don't want them on the Master Tab doing it so if they mess something up it only affects their site.
I have provided a sample and appreciate your assistance. One other thing that I'd like to do is get some help with the PIVOT tab. I tried to create a Pivot table but I'm having issues showing just the filtered data for each column for each site. I'm hoping it's an easy fix but I'd like to track this by Month & Year and I've added a column with Month-Year which seems to work but I can't figure out how to only see the filtered data in the Pivot table. I'll put the date in a separate workbook with just the Pivot Table eventually.
Thanks so much for your help!
Robert
Moderators
January 31, 2022
Offline
Wanting the Master to update the site data and vice versa would require VBA, I believe. And that's something I stay away from.
Why not create a separate file for each site where you can 'touch' all and the site managers only their own. Then use Power Query to combine the Site files into one Master file. Change a site file and refresh the Master and you are set.
Regarding the pivot table, I assume you mean that if, for instance, you filtered the Master to only show data for Site10 and Owner Attribute = Net, that the pivot table and chart should only be based on that data.
One way to achieve that is to add a column to the master data that sets a switch (1 or 0) indicating that the row is visible or not. Then, add a slicer to the pivot table for the "Visible" status. The advantage is that you have only one slicer set to 1 (can be hidden behind the chart), but the disadvantage is that you need to go back and forth between the data to set the filters and the pivot table to refresh it (and the chart) to see the results.
Alternatively you add slicers for every field you would normally filter on. Advantage, you directly control the pivot table and chart with the slicers. Disadvantage, you may end up with many slicers using up a lot of screen space.
The attached file contains an example of both. One with a slicer that lets you select only visible (filtered) rows. The other with (just) two slicers for different elements.
Power Query
Power Pivot
Power BI
Dashboards
November 23, 2023
Offline
Riny,
Thanks for the follow-up. I was hoping this project didn't require VBA so I could keep it in one workbook. 🙁
Why not create a separate file for each site where you can 'touch' all and the site managers only their own. Then use Power Query to combine the Site files into one Master file. Change a site file and refresh the Master and you are set.
I didn't want to make separate files for each site and would like to keep it in one workbook to make it easier to manage and share.
Regarding the pivot table, I assume you mean that if, for instance, you filtered the Master to only show data for Site10 and Owner Attribute = Net, that the pivot table and chart should only be based on that data.
I may want to get that granular eventually but for now I would like to filter on multiple things for each column and show the number and the percentage for each site based on the total number of devices for each month. Please look on the Pivotshouldlooklikethis tab in the attached update. Hopefully, this will make more sense as you can visually see it and I've included which filters are need in each column to get the totals and percentages. Thanks again for your help!
Robert
Trusted Members
October 17, 2018
Offline
Just and idea:
what if you add a worksheet where you have the full path set in a named range and with the new functions available in 365 list all the file snee that you want to read. The only rule you’ll have to agree with the others that all file names have the same prefix and something else.
with the power query or other non VBA method like the more extended functions like LET etc you can retrieve all data in the master sheet whilst using dependent dropdown lists etc.
I don’t have 365 and am better with VBA but it’s just an idea
hope it makes sense one way or another
Moderators
January 31, 2022
Offline
Well, if you don't want VBA but do have Excel for MS365 (as Hans suggested) you could create separate tables for each Site where you do all the changes, and then use VSTACK to create one big dynamic array containing all sites.
Then, regarding the pivot tables you make it complicated by recording a status "Device Name = System Name". But then you want to have a pivot table counting the number of occurrences where the Device Name <> System Name. Much easier to set the flag in the source data to "<>" and use 1 for TRUE and 0 for FALSE. Likewise for flagging a site as Active and wanting to count the Inactive ones. Call the column "Inactive" and use 1 for TRUE and 0 for FALSE. Then the pivot table can simply SUM those fields.
Can't think of an easy way to calculate the percentages for each column, though, in a regular pivot table. I feel you need Power Pivot (PP) for that and a bunch of DAX measures.
Before going that path, you'll need to clarify what Excel version you are using, on what platform (PC, Mac, Web) and where you stand on working with PP and the Data Model.
Otherwise, you might want to re-think and do all your analysis with formula functions like SUMIFS and COUNTIFS.
Answers Post
Power Query
Power Pivot
Power BI
Dashboards
November 23, 2023
Offline
Hans,
Thanks for the feedback. Unfortunately, I don't have the ability to run O365 everywhere and have Excel 2016 as well. 
----------
Riny,
Well, if you don't want VBA but do have Excel for MS365 (as Hans suggested) you could create separate tables for each Site where you do all the changes, and then use VSTACK to create one big dynamic array containing all sites.
Yes, unfortunately, I don't have access to O365 for all networks.
Then, regarding the pivot tables you make it complicated by recording a status "Device Name = System Name". But then you want to have a pivot table counting the number of occurrences where the Device Name <> System Name. Much easier to set the flag in the source data to "<>" and use 1 for TRUE and 0 for FALSE. Likewise for flagging a site as Active and wanting to count the Inactive ones. Call the column "Inactive" and use 1 for TRUE and 0 for FALSE. Then the pivot table can simply SUM those fields.
Good Point on the Device Name = System Name. I have adjusted it and the formulas to reflect Device Name <> System Name = True and same with Active. I changed it to InActive. The system I'm exporting from brought it in like that but I'll update my PowerQuery to make the changes prior to import.
Before going that path, you'll need to clarify what Excel version you are using, on what platform (PC, Mac, Web) and where you stand on working with PP and the Data Model.
Otherwise, you might want to re-think and do all your analysis with formula functions like SUMIFS and COUNTIFS.
I don't have any experience with PP & DAX. I'm using PC and have O365 and Excel 2016. I have the course but I haven't gone through it yet. 🙁 So until I do that I guess I'm relegated to SUMIFS and COUNTIFS. 
Trusted Members
October 17, 2018
Offline
I won’t be able to help you just yet since I’m on a short vacation and no devices except my iPhone.
I’ll take a look when I’m back and see how far you’ve gone and then try some ideas out, at home I only use 2021 on windows and have an older laptop with 2010 to check for compatibility in case older versions are required
1 Guest(s)
