Hi all
I have a workbook with a management dashboard - let's call it the central workbook - that collates commissions and performance data for our sales people from various other workbooks which are managed by leaders. Currently, we keep all of the aforementioned workbooks in a folder that only leaders in our business have access to. This prevents sales people from getting in there and looking at everyone's data. The central workbook updates automatically so any time a leader wants to see what's going on across the business, they just open it and it updates.
We're changing our commissions structure slightly and people will have their own personal commissions workbooks that they'll use to claim commissions with every month. My central workbook still needs to pull all the data from every sales person's personal commissions workbook and this is where I'm a little stuck.
- If people keep their commissions workbooks in their own personal drive or their desktop, the central workbook won't be able to access them.
- If they keep it stored on the common drive, everyone will have access to every person's workbook which we don't want.
- If they store them on the common drive and I password protect everyone's file, automatic updates on the central workbook won't happen and leaders will need to enter every password whenever they want to get an update which is very time consuming.
- If they email their own workbooks to me once a week or once a month, we won't have real-time performance/commissions data for them and there'll be too much manual work in it for me when I get 30+ spreadsheets coming through at once.
The only solution I can see working is to give everyone a folder in the common drive that that only they and authorized leaders have access to (no passwords, I can just ask our IT guys to restrict access to each individual's folder) but I'm not sure if restricting access on each individual's folder will allow automatic updates on the central spreadsheet and it seems like a bit of work to set up and administer such a system.
Maybe I'm making this far more complicated than it is but I can't think of a solution. If anyone has any suggestions, I'd be very appreciative.
Cheers!
I have a workbook with a management dashboard - let's call it the central workbook - that collates commissions and performance data for our sales people from various other workbooks which are managed by leaders. Currently, we keep all of the aforementioned workbooks in a folder that only leaders in our business have access to. This prevents sales people from getting in there and looking at everyone's data. The central workbook updates automatically so any time a leader wants to see what's going on across the business, they just open it and it updates.
We're changing our commissions structure slightly and people will have their own personal commissions workbooks that they'll use to claim commissions with every month. My central workbook still needs to pull all the data from every sales person's personal commissions workbook and this is where I'm a little stuck.
- If people keep their commissions workbooks in their own personal drive or their desktop, the central workbook won't be able to access them.
- If they keep it stored on the common drive, everyone will have access to every person's workbook which we don't want.
- If they store them on the common drive and I password protect everyone's file, automatic updates on the central workbook won't happen and leaders will need to enter every password whenever they want to get an update which is very time consuming.
- If they email their own workbooks to me once a week or once a month, we won't have real-time performance/commissions data for them and there'll be too much manual work in it for me when I get 30+ spreadsheets coming through at once.
The only solution I can see working is to give everyone a folder in the common drive that that only they and authorized leaders have access to (no passwords, I can just ask our IT guys to restrict access to each individual's folder) but I'm not sure if restricting access on each individual's folder will allow automatic updates on the central spreadsheet and it seems like a bit of work to set up and administer such a system.
Maybe I'm making this far more complicated than it is but I can't think of a solution. If anyone has any suggestions, I'd be very appreciative.
Cheers!
)
