The president wants a single, unified view of every manager's opportunities and risks. That's a reasonable ask. But the way it's being built, a separate table on each manager's sheet, with the president manually hoping the data somehow appears on his own, is a recipe for frustration, broken links, and fragile workbooks. This setup treats a fundamentally collaborative problem as a static data-entry task. It's time to rethink the architecture.
What this user has described is a classic "distributed input, centralized output" scenario. Each manager owns their risk table. The president needs a live, read-only snapshot of all those tables combined. The cleanest approach, in our view, is to stop trying to pull data across sheets with complex formulas and instead use a single structured table, a database, really, that lives in one place. That table can have a column for the manager's name, a column for the opportunity or risk, a column for notes, and a column for a timestamp. Each manager gets a filtered view of only their rows. The president gets a full view. No cross-sheet references, no broken ranges, no manual copying.
For the user working in traditional spreadsheets, this means moving away from the "one sheet per manager" layout. It requires a small shift in thinking: instead of building a workbook that mirrors an org chart, build one that mirrors a database. The payoff is immediate. The president's table updates automatically when any manager adds a note. There's no need to write a complex formula that tries to concatenate ranges from ten different sheets. You simply create a master table, give each manager access to their own slice, and use a pivot table or a simple filter on the president's end to see everything.
The practical takeaway is this: stop fighting the tool. If you find yourself trying to pull data from multiple sheets into one, the problem isn't the formula, it's the structure. A single table with a manager identifier column solves the need for cross-sheet formulas entirely. It's simpler to build, simpler to audit, and far less likely to break when someone adds a row to their sheet. That is the kind of solution that makes a president's life easier and your workbook more reliable.