Either Excel or Google Sheets to make file to collaborate with MANY co-workers but with restrictions (Linux, email)

[slapshotbob99]

Hello,

I oversee my company's credit cards, which total about 75. Every month we downloaded a master excel file from the card website, cut it into 75 pieces, and then emailed each employee their card's charges so they can input the correct accounting coding for each line item. This is tedious and time consuming...

Temporarily I created a system in google sheets where a master file that only I have rights to feeds into 75 sub-files that each assigned user can see. This maintains privacy for each person from the other 74 seeing it. So now I don't need to send 75 separate emails each month, but it's still annoying to maintain all these files.

Using either Sheets or Excel, is there a better way? Can I make ONE master file, where I can control which sections each person sees? Perhaps by restricting viewing right to specific tabs or certain "IF" functions?

It also would be important for multiple people to be able to access the file at the same time, since most wait until the last minute to do their sections.


THANK YOU FOR ANY ADVICE!

[gguerra]

Don't know a lot about sheets other than it DOES allow multiple users to edit and collaborate on the same spreadsheet. It may have features like the ones you seek. Just do a little research (google). You do not email copies of the spreadsheet to different people. The same sheet is used by everybody on the cloud and it stays on the cloud for the most part.

[Ed_RDNC]

Perhaps each individual's sheet has a formula so it only displays that Owner's data.

IE
One time setup:
A. Create a master sheet to which you monthly paste the DL data.
B. Within this master sheet, add one tab for each employee. Within that tab, it has a formula linking back to the master sheet and displays only the data for that employee.
C. Restrict general access to the document.
D. Allow specific access by each employee to their tab. Their Sharing-Link should open and allow editing of the master document at their tab only, and restrict access to the other parts.
(see https://mashtips.com/share-specific-...e-spreadsheet/)

Each Month:
1. DL the master sheet.
2. Each tab automatically shows ONLY the data for that employee, to that employee.
3. Save the master sheet under a new name for archival records, and blank the paste-target area to make ready for next month.

[stanley-88888888]

at my job, i have ba's that check health data in spreadsheets. i have a linux-vm and i wrote a bash script to query it from the mainframe, download the dataset import into xml format then email the resultant .ods files to the requestors.

now that the newest version of microsoft excel supports ods, i find it to be the most portable format and easiest to hax with (much more saner than xls format).

[slapshotbob99]

Originally Posted by Ed_RDNC

Perhaps each individual's sheet has a formula so it only displays that Owner's data.

IE
One time setup:
A. Create a master sheet to which you monthly paste the DL data.
B. Within this master sheet, add one tab for each employee. Within that tab, it has a formula linking back to the master sheet and displays only the data for that employee.
C. Restrict general access to the document.
D. Allow specific access by each employee to their tab. Their Sharing-Link should open and allow editing of the master document at their tab only, and restrict access to the other parts.
(see https://mashtips.com/share-specific-...e-spreadsheet/)

Each Month:
1. DL the master sheet.
2. Each tab automatically shows ONLY the data for that employee, to that employee.
3. Save the master sheet under a new name for archival records, and blank the paste-target area to make ready for next month.

Thanks. For step B, is there a way to lock that formula? If we set up one tab to show data for: "IF John Smith, show..." Can't John Smith just change the formula to: "IF Tom Jones" if he gets curious about Tom's expenses?


And.... I know this one is a reach: When all 75 users have filled out their coding, I plan to link all 75 individual tabs to link into a "Final" Tab and stack one on top of the other. Our intern figured out a way to do that, but it involves 75 formulas added together in the formula box. Is there a simple way to make "all tabs" accumulate into one Final tab?

thanks.

[Ed_RDNC]

Originally Posted by slapshotbob99

Thanks. For step B, is there a way to lock that formula? If we set up one tab to show data for: "IF John Smith, show..." Can't John Smith just change the formula to: "IF Tom Jones" if he gets curious about Tom's expenses?

This is easy, set the workbook to be Protected, (which enables passwording).
Then you can password protect individual cells, and individual tabs, leaving unprotected the cells where you need others to do input.

Originally Posted by slapshotbob99

And.... I know this one is a reach: When all 75 users have filled out their coding, I plan to link all 75 individual tabs to link into a "Final" Tab and stack one on top of the other. Our intern figured out a way to do that, but it involves 75 formulas added together in the formula box. Is there a simple way to make "all tabs" accumulate into one Final tab?thanks.

This is also easy (but not obvious).
You designate a formula that contains a range of cells, and that range is only in the "3rd dimension' of multiple tabs.
To subtotal across the range of multiple tabs, just go to the first tab with this data, select that cell, then highlight additional tabs. That range selected will be expanded across those multiple tabs.
This method has the additional benefit of being dynamic. IE: If you insert a new tab (for a new employee), within that range, it will be automatically included in the subtotaling. The manual formula listing the 75 users would have to be manually edited for when you get a 76 person. Likewise, if one of the 75 resign, all you have to do is delete the tab and the subtotaling adjusts automatically.