I work with 100 people, and I have their details in a single Microsoft Excel document.
Each row represents each user with his/her details in a single row. Now I want to share the document with users, but I do not want them to see all details of other users on the document. I want them to be able to log in to a single row and see only details in that row.
This is not possible. You either give users access to the document or not.
What you want to do instead, is link several sheets to a master sheet.
You can link data from a cell in one sheet to a cell in another sheet.
Simply open 2 sheets, select the cell you want the data to be in, press = and then click on the cell you want it to be linked to in the other sheet and press enter.
It will automatically create a formula for you that shows whatever is in the other sheet.
Your users will work in their individual sheet, and you use the master sheet to work with the data they are entering (like adding totals, averages, etc).
There seems to be a little bit of confusion in the comments on how this would work from a security point of view.
Therefor this edit.
Lets say, you have a network share with the following 2 folders:
N:\Report\Users\#######
N:\Report\Management
Inside ###### is an excel folder for each person, and rights are setup so managemant and that user can access that folder.
Management only has access to the Management folder.
In the Management folder, links are created to each users sheet, to different cells.
The user edits their sheet or can view it, or however you want to do it.
When you open the management sheet, it asks you if you want to refresh the data. All cells are updated and recalculated.
A user can only access the sheet in their folder, thus they cannot see the rest. Given they have no access to the management folder, they cannot access that data either.
I don't think Excel has functionality to do what you want directly.
Here's what I did years ago when I had to solve a similar problem. The solution I implemented was a web page with a PHP script powered by PHPExcel (now PHPSpreadsheet). So
You need to install a web server (IIS comes by default in Windows. It just needs to be configured; or one can easily install Apache or a similar one).
A very simple web page with a form that requests the identification from your co-workers and calls the PHP script.
the PHP script that parses the input and accesses the spreadsheet via PHPSpreadsheet's API, and presents the data in the web page.
The whole thing (web page + PHP script with the calls to get the data from the spreadsheet + a basic logic to display the data) is about 120 lines. Almost half of that is the particular logic I needed to implement in my case, but it could be almost nothing if you are displaying data straightforwardly.
This is not possible without special extensions.
However, you could create a spreadsheet which pulls information from many other spreadsheets. This may be fairly slow, so you may find using VBA or a scripting language (such as Python) to read the files in parallel and handle access issues (user may have the file open/locked, corrupt data, poor network connections..).
Do also note that this feels like poor practice and is really an XY Problem. If you need to store data and permit only restricted access to it, try a webserver. Hosting Wordpress or similar may get you started!
