0

this may be open-ended but let me put this forward.

I have a very complex dashboard which works out staff hours across the country. In this dashboard our team members have a drop down to choose their store which populates the same sheet with data relevant to their store, all to do with staff hours.

Now historically, the teams have used excel to do this but due to excels limitations and the amount of time it takes to update the data when changing the drop down (seconds in google sheets and about 5 minutes per drop down in excel!) I have decided to move to google sheets.

the issue lies when we have multiple users using the same sheet, if two of my area managers used the sheet at the same time then they would essentially be fighting for to see their data. With excel we would just email out the report and everyone would have their own copy. So what I'm after is a filter views sort of solution.

I've thought of the following:

Create n number of sheets in the same workbook using javascript to parse through an array of Area Names whilst duping and changing the reference cell to the cell reference in the array. I could then create a front page in google sheets which links to each page like a table of contents.

Issues with this is that the workbook would become massive and I may go past my 2mil cell limit.

I really do not know of any other way except for perhaps getting a BI tool like PowerBI and creating a dashboard there and putting the data into SQL server.(which I cannot do for the mo as I've just started and we don't have PBI or sharepoint yet!)

Does anyone have any pointers or guidance to the above?

  • 1
    how about to have a master sheet and create slave sheets which will use IMPORTRANGE from a master and each of your managers gets 1 such slave sheet which can be filtered as pleased – user0 Aug 20 '18 at 18:33
  • 1
    Hi. A little confused by your comment about two area managers fighting over data. Have you tried filter views? I have a sheet that is similar to your situation. I have different users that need to look at different sets of data, so I have specific filter views for each user set, and the views don't interfere with any other user's view. The IMPORTRANGE idea is good also. I used that for specific data to format it for printing. The only problem is editing, which is not possible in the imported data. – Ron Kloberdanz Aug 20 '18 at 18:41
  • The problem is data isn't filtered, it's organically summed from each change of the area id drop down. I'll have a play with filtered views again. With import range, forgive me if Im mistaken but import range does not allow the user to edit the sheet. I guess I could make a bunch of slave sheets which use import range before the queries.. (not looking forward to that...!) – Datanovice Aug 20 '18 at 21:20
  • I think @User0 has the most appropriate solution before we migrate to PowerBI. i'll give it a go using some basic JS. – Datanovice Aug 21 '18 at 9:01
  • 1
    adding it as an answer unless someone comes up with something else... – user0 Aug 21 '18 at 9:07
0

how about to have a master sheet and create slave sheets (or spreadsheets) which will use =IMPORTRANGE from a master and each of your managers gets 1 such slave sheet (or spreadsheet) which can be filtered as pleased

then such sheets can be locked per manager and act as "dedicated filtering sheet" for such manager while master can be editable

  • Cheers User0, using this as my solution for the time being – Datanovice Aug 21 '18 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.