I am creating a workbook which will live in SharePoint and will be used as a register to hold sales data etc.
I need to create a couple of additional workbooks which will be dashboards to show some data and reports from the main register, these will be local files for people to keep on their laptops. These are all based on pivot tables from the main register.
What would be the best way to create the connection between the dashboard and the register? I've looked at data connections, or just selecting the range in the register when creating the pivot, which would be best? If the data connection, I would appreciate some help in creating the connection if possible as I'm not sure about how to do this with SharePoint.
IMHO, the best way is to connect and transform data using Power Query, add some measures in data model using Power Pivot and finally pivot into Excel dashboard. Even more better to publish dashboards on Power BI services.
However, that requires to invest some time in learning of above tools if you are not familiar with them.