U.S. CitiesCity-Data Forum Index
Go Back   City-Data Forum > General Forums > Science and Technology > Computers
 [Register]
Please register to participate in our discussions with 2 million other members - it's free and quick! Some forums can only be seen by registered members. After you create your account, you'll be able to customize options and access all our 15,000 new posts/day with fewer ads.
View detailed profile (Advanced) or search
site with Google Custom Search

Search Forums  (Advanced)
 
Old 02-01-2018, 09:24 PM
 
235 posts, read 186,817 times
Reputation: 322

Advertisements

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!
Reply With Quote Quick reply to this message

 
Old 02-02-2018, 08:32 AM
 
Location: McAllen, TX
3,972 posts, read 2,617,954 times
Reputation: 4741
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.
Reply With Quote Quick reply to this message
 
Old 02-02-2018, 09:47 AM
 
Location: Raleigh, NC
2,296 posts, read 3,489,851 times
Reputation: 3012
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.
Reply With Quote Quick reply to this message
 
Old 02-02-2018, 08:59 PM
 
Location: (six-cent-dix-sept)
4,887 posts, read 2,501,496 times
Reputation: 3147
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).
Reply With Quote Quick reply to this message
 
Old 02-20-2018, 12:34 PM
 
235 posts, read 186,817 times
Reputation: 322
Quote:
Originally Posted by Ed_RDNC View Post
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.
Reply With Quote Quick reply to this message
 
Old 02-21-2018, 08:46 AM
 
Location: Raleigh, NC
2,296 posts, read 3,489,851 times
Reputation: 3012
Quote:
Originally Posted by slapshotbob99 View Post
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.

Quote:
Originally Posted by slapshotbob99 View Post
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.
Reply With Quote Quick reply to this message
Please register to post and access all features of our very popular forum. It is free and quick. Over $68,000 in prizes has already been given out to active posters on our forum. Additional giveaways are planned.

Detailed information about all U.S. cities, counties, and zip codes on our site: City-data.com.


Reply
Please update this thread with any new information or opinions. This open thread is still read by thousands of people, so we encourage all additional points of view.

Quick Reply
Message:

Over $104,000 in prizes was already given out to active posters on our forum and additional giveaways are planned!

Go Back   City-Data Forum > General Forums > Science and Technology > Computers
Similar Threads
Follow City-Data.com founder on our Forum or

All times are GMT -6. The time now is 02:26 AM.

© 2005-2019, Advameg, Inc. · Please obey Forum Rules · Terms of Use and Privacy Policy · Bug Bounty

City-Data.com - Archive 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35 - Top