Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-15-2023, 03:57 PM
Marcia's Avatar
Marcia Marcia is offline How to share Excel data to individuals Windows 11 How to share Excel data to individuals Office 2021
Expert
How to share Excel data to individuals
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default How to share Excel data to individuals

Hi all. I am in charge of preparing the payroll of about 98 employees using Excel. Is there a way to share the payroll sheet to evryone but each employee can only access his own pay data?
Thank you.
Reply With Quote
  #2  
Old 05-16-2023, 12:08 AM
ArviLaanemets ArviLaanemets is offline How to share Excel data to individuals Windows 8 How to share Excel data to individuals Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Theoretically something like this may work:
You have the payroll table in sheet set as very hidden. In leftmost column of payroll table are stored LogIns of employees;
Have your VBA Project protected with password (so some employee more proficient with Excel can't remove the Very Hidden setting from Payroll sheet). And have the Payroll sheet named in way, users can't guess it easily (like some meaningless string);
Create an UDF, which returns the LogIn of current user (this is a step because of which I used "Theoretically" - I have never needed to do this in Excel, but I have a feeling I have read about it somewhere.);
Have a single visible sheet, and into some cell enter the formula for user's LogIn;
Use VLookup formulas, to get all payroll data for current user from payroll table on very hidden sheet;
Protect the visible sheet, where current user payroll info is displayed, with password, setting formulas hidden (so user can't see the name of very hidden sheet in formula).
Reply With Quote
  #3  
Old 05-16-2023, 01:28 AM
Marcia's Avatar
Marcia Marcia is offline How to share Excel data to individuals Windows 11 How to share Excel data to individuals Office 2021
Expert
How to share Excel data to individuals
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you Arvi. I got your idea and I think I can do the hide sheets. Instead of VLookup formula, I think Pivot is more flexible.
The tricky thing for me is the users log in and passwords because I have a very limited knowledge on programming. I will google search and hope to find possible solutions.
Reply With Quote
  #4  
Old 05-16-2023, 03:21 AM
ArviLaanemets ArviLaanemets is offline How to share Excel data to individuals Windows 8 How to share Excel data to individuals Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by Marcia View Post
Instead of VLookup formula, I think Pivot is more flexible
In attachment it is done with VLOOKUP. I think it is impossible to get it more flexible - the only variable in VLOOKUP formula is the number of column the value is returned from payroll table. In payroll table, you can have any number of columns, and have any headers you want for them.

I dropped all hiding and protecting stuff currently - I think you can do this part yourself, after you design both sheets properly.

The payroll table is an traditional one - when a defined Table is used instead, then there will be no way to make for users reading payroll of other employees impossible, because the Name of Table will be visible in Name Manager window!
Attached Files
File Type: xlsm PayrollTest.xlsm (16.5 KB, 2 views)
Reply With Quote
  #5  
Old 05-16-2023, 04:18 AM
Marcia's Avatar
Marcia Marcia is offline How to share Excel data to individuals Windows 11 How to share Excel data to individuals Office 2021
Expert
How to share Excel data to individuals
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you Arvi. You really go the extra mile in sharing your ideas and expertise. I will edit our actual payroll then attach it in this same thread.
Reply With Quote
  #6  
Old 05-16-2023, 06:24 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How to share Excel data to individuals Windows 10 How to share Excel data to individuals Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Why do the employees need to access an XL file?
Why not create a slip for each one of them using Word's Mail Merge ?
There's certain a lot of info in the Word subforum to help you out. Macropod and Charles are real wizards in that domain
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 05-16-2023, 03:02 PM
Marcia's Avatar
Marcia Marcia is offline How to share Excel data to individuals Windows 11 How to share Excel data to individuals Office 2021
Expert
How to share Excel data to individuals
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Pecoflyer View Post
Why do the employees need to access an XL file?
Why not create a slip for each one of them using Word's Mail Merge ?
A few years back, we give to the employees their monthly pay slips generated through mail merge. Then they requested that we issue slips showing the months and the cumulative 1totals. The slip in April shows the columns Jan, Feb, Mar, Apr and Totals. Pivot can do the job so we no longer use mail merge.
We would like to go paperless as much as practically possible, hence the idea of sharing the payroll but an employee can only access his own pay details.
Thank you.
Reply With Quote
  #8  
Old 05-20-2023, 01:04 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How to share Excel data to individuals Windows 10 How to share Excel data to individuals Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

I still think that asking the question on the Word subforum might get you help
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #9  
Old 05-20-2023, 03:25 AM
Marcia's Avatar
Marcia Marcia is offline How to share Excel data to individuals Windows 11 How to share Excel data to individuals Office 2021
Expert
How to share Excel data to individuals
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

I will do that. Thank you.
Reply With Quote
  #10  
Old 05-20-2023, 03:34 AM
Marcia's Avatar
Marcia Marcia is offline How to share Excel data to individuals Windows 11 How to share Excel data to individuals Office 2021
Expert
How to share Excel data to individuals
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

I will do that. Thank you.
Reply With Quote
  #11  
Old 05-21-2023, 01:17 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How to share Excel data to individuals Windows 10 How to share Excel data to individuals Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

And if you do, please add a link?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Generating reports that shows an individuals planning per week Zweebo Project 1 02-18-2023 10:52 PM
share excel pdf format to email jmcsa3 Excel Programming 0 03-14-2020 10:50 AM
How to distribute a list of 400 among 27 individuals Tesla Excel 5 02-21-2019 02:22 AM
How to share Excel data to individuals How to share cell data between tabs of workbook? littlekeith Excel 1 03-23-2015 06:21 AM
Share my data on via MYSQL??? cwksr Excel 0 08-24-2014 03:23 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft