My ceo asks for an overview of all sales areas containing their pipeline as well as current customers. Including several calcualtions on individual contractual stuff such as deadlines for changing the prices etc.
I have previously created this for 1 area, now I must scale it the whole company. I consider... what is the best method for this? The sales areas are only allowed to see their ovn data and are also expected to keep own data updated. And my ceo of course wants the full picture.
If not for the security I would just build one full file containing a sheet for each area and a consolidation sheet for ceo. This could cause issues on several users wanting to edit at the same time (1 hour before deadline :-))
Even that users are not allowed to see other areas data, I also have in mind that this is an very internal tool, and I must rely that colleagues are not trying to hack other areas passwords if these are set on sheet level. If the tool gets more clean this way, we go there.
I prefer to have the file(s) stored on our server, which everyone can access. Not the fancy online stuff, which I find hard to manage quite as well as my desktop Excel.
Biggest concern is that the consolidation sheet are able to refresh all data correct.
Any advice for me?
BR
Susanne
Without seeing your file or knowing exactly what you are dealing with, I would create one file for each sales area. All looking the same and protect in such a way that they can't change the structure / lay-out of whatever data is collected in these files.
Then I would use Power Query to connect to each of the area files, extract the relevant data and combine it all into one file for the CEO. Refresh the connections when updates have been entered into the area files.
Thanks for giving some thoughts. I just wasn't sure if I overlooked another method.