Best way to collaborate on a dashboard?

[reitman1]

Hi all. Thank you for help in advance. I need some assistance with the best way to have my dashboard workbook reference several other workbooks. Let me give you a little background on my situation. I help manage several retail stores. At each store, we have an individual excel spreadsheet that the managers enter information on throughout the day. I have setup a dashboard spreadsheet that references each individual workbook that the stores produce. The way I am currently handling updating the dashboard is to have each manager email their spreadsheets at the end of each day and I overwrite the old versions in the same directory that I have the dashboard spreadsheet saved in. As we continue to expand, I have been asked by the owners if there is any way to allow the dashboard spreadsheet to be updated in real time as opposed to at the end of the day because some of the information would be useful if updated throughout the day.


I was wondering if anyone had any experience or tips focusing on this situation. I have explored a couple of options, but can’t get anything that’s fool proof just yet. I have tested Dropbox, but for the update to take place, all spreadsheets have to be saved and closed and then reopened in order for the Dashboard sheet to update. I do have administrative access to a Sharepoint server, but have not figured out how to get excel to reference spreadsheets that I have stored on the server.


If anyone could provide any insight, I would appreciate it.

 

[dan_l]

Access. (my answer to everything).


Well, actually, any database or database like structure will work. Maybe there's something you could build on sharepoint. This method will allow your users to plop the data into some sort of central pool, and you won't have to worry too much about endless emails with spreadsheets and spreadsheets to track the spreadsheets, and people to manage the spreadsheets that track the spreadsheets and to do lists and yada yada...you get the point.


This will have value to your dashboard:


-You can systematically check the data to make sure that the input you're getting is actually what you're looking for.


-You'll have really awesome fine point control over what data comes into your dashboard and - just as importantly - how it comes into your dashboard.

 

[reitman1]

Dan,


Would you be able to point me towards any tutorials that might help me with setting up a database on Sharepoint that I would be able to pull data from. Currently my knowledge of Sharepoint is essentially limited to setting up sites to segregate the stores and giving users access to the individual sites. Thanks so much for your help.

 

[dan_l]

Good question.


I'm really not sure.


I would probably just throw an access database out there. You should be able to find it using MS query for your dashboard. Getting the data in there could be as simple as creating a user form (easy) and submitting the record via ado (also easy). You wouldn't even have to have everybody with a native access install.


I'll play with sharepoint and see what I can see.

 

[jen_hal]

I've never used sharepoint, but would also like to do somewhat of the same. Be able to create a dashboard that refers to several charts from separate teams within our department. If sharepoint is the way to go, how can I get further information on this? However, since all spreadsheets are on network/in house, can I create a Macro that refers to those spreadsheets for updating?


One last question - if that is the case - how do I create my macro to say - I want the next cell from previous update i.e. new week's percentage and also percentage to move to prior from last week's percentage?