Cross Sheet References to an Empty Sheet
I'm building a master metrics / rollup sheet for a portfolio of projects. Each project has it's own workspace and a set of sheets (project plan, risk log, issue log, etc). The rollup sheet has one row per project and each column represents a metric, for example, High Severity Risks.
The approach I've taken is, in the case of risks, count the number of open risks for each project using cross-sheet formula. This works great until I reference a project's risks log that has now risks (i.e. empty) - a message appears saying "The sheet you selected is empty. Please select a different sheet".
The workaround I've used is to enter a dummy record, but it's not very elegant or scalable, as I'd need to enter dummy records in every new risk, issue, decision, etc sheet that gets created for every project.
Is there some other approach anyone could suggest?