8

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.

  • 2
    You could do this with VBA. Ensure each row has a column with the people's usernames. Hide all the rows and password-protect the sheet. Have a Worksheet_Open event, which takes the UserName environment variable, and searches for a match in the relevant column. Once the row is identified, un-hide that row via VBA. It's well worth embarking on a VBA-learning journey: you'll be able to do virtually anything in Excel. – Chris Melville Mar 16 at 12:13
  • 7
    @ChrisMelville that is a really bad idea. The only way this will work, is if the password is directly in the macro itself. So the first thing I'd do is press ALT-F11, access the VBA editor, check the macro, get the password, then unlock the document myself, there, full access. – LPChip Mar 16 at 13:42
  • 4
    @LPChip - It depends on the level of security you're after. Excel was never designed to be highly secure against knowledgeable experts with intent. From how I interpret the OP's situation, they just want to block casual snooping & tinkering - and Excel's capacity is just fine for this. You can lock the VBA project to prevent Alt+F11 review. Yes, I know there are ways round this - but it depends on your risk profile. Unless you're securing top secret information from a bunch of hackers, Excel security does the job. – Chris Melville Mar 16 at 13:51
  • 6
    A spreadsheet is not a database (nor is it a place for writing documentation). It sounds more like you want a web site (you mention 'login'). – Neil Mar 16 at 15:16
  • 1
    How important is it that they only see their own data? Is it a convenience for them and no big deal if somebody clever snoops, or is it confidential data that needs to be kept confidential? – Kat Mar 16 at 18:27
27

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.

| improve this answer | |
  • 3
    What prevents a user from changing the row in their references and looking at other people's data? – Martin Argerami Mar 16 at 12:58
  • 2
    Concretely, I'm curious how you can grant me access to use a spreadsheet that grabs data from another one, without allowing me to access that second spreadsheet. – Martin Argerami Mar 16 at 13:54
  • 2
    @MartinArgerami it's the opposite of that. – hobbs Mar 16 at 15:29
  • 4
    @MartinArgerami yes, he wants something that is not possible, so I suggested him an alternative that will work. This is how it is being done in most major companies around the world. – LPChip Mar 16 at 15:58
  • 7
    "This is how it is being done in most major companies around the world" Um, citation needed? By which I mean, no it isn't. – Jonathan Hartley Mar 16 at 21:04
1

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!

| improve this answer | |
1

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.

| improve this answer | |
  • Re "web server ... it comes by default in Windows.": Are you sure about that? On my Windows 10 Home system (that is now collecting dust because I switched to Linux), the result of any of http://127.0.0.1, https://127.0.0.1, and http://127.0.0.1:80 in Firefox is "Unable to connect". Are you sure it isn't Visual Studio, Jupyter Notebook, or similar that installed a web server? What program is answering on port 80 on your system? – Peter Mortensen Mar 16 at 18:13
  • It's IIS, and according to superuser.SE, it can be activated on Windows Home. I don't think it comes activated by default (that wouldn't be nice), but in most versions of Windows it can be activated from "Programs and Features". I have used it (in Windows Pro, mind you) in the past, since at least XP. – Martin Argerami Mar 16 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.