I have two Excel files on SharePoint

  1. A source file which only I have permission to view/access and
  2. A public file that can be accessed by multiple people.

There is a VLOOKUP in the "public file" which retrieves information from the protected "source file". When other people open up the "public file" the VLOOKUP does not properly update the data because they do not have permission to view the "source file".

Is there a way to set permission so that the data can pass through but the file is still protected?

Any help is appreciated!


That depends on what level of protection you need. If the Excel file has a workbook password to even open and read, then no, that won't fly.

Consider removing the workbook password and use sheet protection instead. People will be able to use formulas that reference locked cells on a protected sheet. If they open the workbook they will not be able to make changes to locked cells on the protected sheet.

If you don't want them to even see data when they open the workbook with the protected sheet, there are a few ways to handle that. If you need help with that, please pipe up.

Edit after comment: here are a few suggestions

  • hide the lookup sheet. Vlookup works with hidden sheets
  • insert a few empty columns to the left of the lookup table, so the lookup table is no longer visible on the screen. Format all cells as "locked" and protect the sheet, untick the option that allows selecting locked cells.
  • format all cells with custom format ;;;, which will not display anything in the cells. Lock and protect as in the previous step

Note that a savvy Excel user will be able to work around these steps. Sheet protection is meant to prevent inadvertent changes, not malicious intent.

  • Thanks for responding! Yes, that's correct I don't want them to see the data in the "source file". They should be able to see it in the "public file" if it is pulled via VLOOKUP. I have not set a workbook password. I was trying to control things via SharePoint permission levels but am open to other methods. – Jay P. Feb 5 '16 at 17:51
  • I added a few suggestions. – teylyn Feb 5 '16 at 20:31
  • Thank you again for replying. Unfortunately most of your suggestions won't work. I do have a few users who do actually need to be able to see the data in the "source file" so hiding the sheet or the data in the cells is not an option. That is why I was initially trying to manage visibility via SharePoint Permissions rather than doing anything to the excel sheet itself. – Jay P. Feb 5 '16 at 21:56
  • Why don't you step back, take a breath and then outline the complete requirements, without thinking about a particular solution. Your requirements seem to change with every comment. At best, that is a waste of time trying to answer. – teylyn Feb 6 '16 at 6:55

A paste special can be used once you've created a vlookup spreadsheet and save it under a different name for others to view.

Your Answer

