I would like to provide a workbook to several team members with some data validation and vlookups that depend on data on a table. However, I do not want people to have access to the full table information. Is that possible at all?
I tried putting the table on very hidden sheet. That prevents the table from showing when trying to use Power Query from another file to extract the data from this file. However, the table name still shows on the dropdown list in the Names box on the left of the formula bar.
By knowing that the table exists and what its name is, I can use an array formula to extract the whole table to another book. I can also use VBA to list all the table names on the workbook.
Any help with this would be very much appreciated.
Your only really safe option is to make sure the table contains information the user in question is allowed to see. Any VBA savvy person will be able to get at the information one way or another. Worksheet protection and workbook protection are not enough either as both can very easily be broken into. I'd suggest placing the data in a database with proper row-level security and user roles setup and then create a connection to that table which refreshes on file open.