Pulling data through from another worksheet on another Team...permissions issue

[clairefct]

Hello there,

I work for a small charity and we are trying to improve our data management but are facing a conundrum...

• Advisors (deal directly with service users) + Chairs (do not but need to keep abreast of data).
• Sensitive data is stored in a password protected Excel worksheet (Advisor worksheet) which sits in the Advisors Team.
• Only advisors have access to this Team, not Chairs.
• Non-sensitive data (e.g. case numbers and dates) are extracted from these worksheets into another worksheet (Review Worksheet) in the same Team which updates quarterly and year-to-date figures in real time. No sensitive data in the Review Worksheet.
• The Chairs need access to the Review Worksheet (to keep abreast of case numbers etc.) but not the Advisor worksheet.

I have tried:

• Copying the Review Worksheet into another Team in our organisation (to which the Chairs have access) but it no longer pulls the data through from the Advisor Worksheet.
• Changed the Advisors to full access (in Advisors Team) and added a Chair as restricted access (Review worksheet only, not Advisor Worksheet) ... but the Review Worksheet will not update when accessed by Chair who doesn't have permission to the Advisor Worksheet...the review worksheet shows the following error: 'UNABLE TO REFRESH - We couldn't get updated values from a linked workbook.' Presumably this is because the Chairs do not have permission to view the Advisor worksheets and so the data being pulled through is also considered not permissible to view?

Does anyone have any idea of if it is possible for the Chairs to get access to the Review Worksheet when they are not permitted to view the Advisor Worksheet?

Many thanks for any help!