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.

Peter Mortensen
  • 12,090
  • 23
  • 70
  • 90
Matthew Mataruse
  • 97
  • 1
  • 2
  • Alternatively, if you know (or can know) the users requesting the data, why not just write a macro to say auto-email the user their respective line's info? – BruceWayne Mar 16 '20 at 05:09
  • 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 '20 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 '20 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 '20 at 13:51
  • I also wanted to note more broadly, and expand a tad on @LPChip, but depending on the sensitivity of the data, password protecting may be a false sense of security. Passwords in Excel are relatively easy to eschew/break/hack. – BruceWayne Mar 16 '20 at 13:55
  • 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 '20 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 '20 at 18:27
  • I think it would be easier to have a single file for each person – user13267 Mar 17 '20 at 02:38

3 Answers3

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.

LPChip
  • 59,229
  • 10
  • 98
  • 140
  • 3
    What prevents a user from changing the row in their references and looking at other people's data? – Martin Argerami Mar 16 '20 at 12:58
  • They can indeed alter their row. But their row is the only thing they see, so they cannot see other people's data unless you allow them access to that sheet. You can of course also stop them from making changes to the sheet by various means. For example, a password to view the sheet and a different one to edit the sheet, NTFS rights to edit the file, protect cells, then lock with password, etc... – LPChip Mar 16 '20 at 13:41
  • I would mention that in your answer, because to me it looks like the essential point. I'm not particularly acquainted with password protection in Excel, so I don't know what can and cannot be done. – Martin Argerami Mar 16 '20 at 13:44
  • 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 '20 at 13:54
  • even with password protection, a user could always type =locationofmastersheet!A1 and drag down and across and reveal the whole master sheet – PeterH Mar 16 '20 at 14:35
  • 2
    @MartinArgerami it's the opposite of that. – hobbs Mar 16 '20 at 15:29
  • @hobbs: in that case, this answer does not answer the original question. – Martin Argerami Mar 16 '20 at 15:37
  • @MartinArgerami yes it does, you just don't seem to understand how it works. – LPChip Mar 16 '20 at 15:50
  • @LPChip: unless I'm missing something substantial, you propose for OP to have a spreadsheet that picks data from all the users' spreadsheets. As I understand it, OP wants the opposite: he wants the users to be able to see a bit of concrete data from his spreadsheet. – Martin Argerami Mar 16 '20 at 15:56
  • 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 '20 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 '20 at 21:04
  • @JonathanHartley I speak out of my own experience. Can't quote that, so just because you don't know this is the case doesn't mean it is not the case. – LPChip Mar 16 '20 at 21:50
  • Hey @LPChip. I'm sorry to be so antagonistic, but you don't have experience at most major companies around the world, so I don't understand how you can vouch for that. – Jonathan Hartley Mar 17 '20 at 14:43
  • @JonathanHartley and you know that because...? I reason that most companies follow this way of working, because it is a best practice. I have worked with many companies around the world to migrate from situations like I suggest here towards a program designed to handle it better. So this is a situation from. It is a best practice if you only want to use office and no additional programs. – LPChip Mar 17 '20 at 15:27
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.

Martin Argerami
  • 800
  • 7
  • 17
  • 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](https://en.wikipedia.org/wiki/Microsoft_Visual_Studio), [Jupyter Notebook](https://en.wikipedia.org/wiki/IPython#Project_Jupyter), or similar that installed a web server? What program is answering on port 80 on your system? – Peter Mortensen Mar 16 '20 at 18:13
  • It's IIS, and [according to superuser.SE](https://superuser.com/questions/1245472/does-windows-10-home-edition-have-iis), 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 '20 at 18:56
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!

ti7
  • 236
  • 2
  • 8