The workaround you built, a new tab, Power Query, a connection chain, formulas that go blank unless the source is open, is clever. It is also fragile, frustrating, and exactly the kind of patch that keeps teams stuck in manual overhead instead of moving forward. You deserve a better solution, and the fact that you are asking for one tells us you already know the current setup is not sustainable.
The core issue here is not your technical skills. It is that the tools you are using were not designed for this problem. Power Query is powerful, but it assumes you control the data pipeline. When permissions split your data across SharePoints, when some files are off-limits to your team, when formulas break because a workbook is closed, you are fighting the tool's assumptions rather than letting it work for you. Your instinct to pull only the columns your team needs is smart. The limitation is that traditional spreadsheets treat access as an all-or-nothing proposition: either you see the whole sheet, or you see nothing. That is not how modern teams should work.
What you are describing is a data governance problem dressed up as a spreadsheet error. An AI-native approach would let you define exactly which columns and rows your team can see, regardless of where the source data lives or who owns the parent file. Instead of building a fragile bridge between two permissioned zones, you would simply set a rule: "HR-adjacent team sees columns A, C, and E from the master table, and nothing else." No workaround tabs. No blank formulas. No dependency on the main sheet being open. The tool handles permissions at the data level, so your team gets only what they need, when they need it, without touching the rest.
The practical takeaway is this: stop optimizing a workaround that was never meant to scale. The next time you find yourself building a multi-step connection chain just to hide a few columns, ask whether the tool is bending to your workflow or the other way around. The solution is not a better macro or a smarter formula. It is a system that treats data access as a configurable rule, not a manual patch.