#1
|
||||
|
||||
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. |
#2
|
|||
|
|||
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). |
#3
|
||||
|
||||
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. |
#4
|
|||
|
|||
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! |
#5
|
||||
|
||||
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.
|
#6
|
||||
|
||||
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 |
#7
|
||||
|
||||
Quote:
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. |
#8
|
||||
|
||||
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 |
#9
|
||||
|
||||
I will do that. Thank you.
|
#10
|
||||
|
||||
I will do that. Thank you.
|
#11
|
||||
|
||||
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 |
|
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 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 |