Home

Prevent access to table data on a workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-680823%22%20slang%3D%22en-US%22%3EPrevent%20access%20to%20table%20data%20on%20a%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-680823%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3EI%20would%20like%20to%20provide%20a%20workbook%20to%20several%20team%20members%20with%20some%20data%20validation%20and%20vlookups%20that%20depend%20on%20data%20on%20a%20table.%20However%2C%20I%20do%20not%20want%20people%20to%20have%20access%20to%20the%20full%20table%20information.%20Is%20that%20possible%20at%20all%3F%3C%2FP%3E%3CP%3EI%20tried%20putting%20the%20table%20on%20very%20hidden%20sheet.%26nbsp%3BThat%20prevents%20the%20table%20from%20showing%20when%20trying%20to%20use%20Power%20Query%20from%20another%20file%20to%20extract%20the%20data%20from%20this%20file.%20However%2C%20the%20table%20name%20still%20shows%20on%20the%20dropdown%20list%20in%20the%20Names%20box%20on%20the%20left%20of%20the%20formula%20bar.%3C%2FP%3E%3CP%3EBy%20knowing%20that%20the%20table%20exists%20and%20what%20its%20name%20is%2C%20I%20can%20use%20an%20array%20formula%20to%20extract%20the%20whole%20table%20to%20another%20book.%20I%20can%20also%20use%20VBA%20to%20list%20all%20the%20table%20names%20on%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20with%20this%20would%20be%20very%20much%20appreciated.%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3ECelia%20Alves%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-680823%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-686254%22%20slang%3D%22en-US%22%3ERe%3A%20Prevent%20access%20to%20table%20data%20on%20a%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-686254%22%20slang%3D%22en-US%22%3EHi%20Celia%2C%3CBR%20%2F%3E%3CBR%20%2F%3EYour%20only%20really%20safe%20option%20is%20to%20make%20sure%20the%20table%20contains%20information%20the%20user%20in%20question%20is%20allowed%20to%20see.%20Any%20VBA%20savvy%20person%20will%20be%20able%20to%20get%20at%20the%20information%20one%20way%20or%20another.%20Worksheet%20protection%20and%20workbook%20protection%20are%20not%20enough%20either%20as%20both%20can%20very%20easily%20be%20broken%20into.%20I'd%20suggest%20placing%20the%20data%20in%20a%20database%20with%20proper%20row-level%20security%20and%20user%20roles%20setup%20and%20then%20create%20a%20connection%20to%20that%20table%20which%20refreshes%20on%20file%20open.%3C%2FLINGO-BODY%3E
Frequent Contributor

Hello!

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.

Thank you,

Celia Alves

1 Reply
Hi Celia,

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.
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies