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?

Find more posts tagged with

Accepted answers

Paul Newcome

I generally use a placeholder row in the template set that basically just says something along the lines of "This row is used for formatting. Please do not delete or edit". This way it is automatically in every new project created.

How often do you create new projects? Have you looked into Control Center?

All comments

Paul Newcome

How often do you create new projects? Have you looked into Control Center?

Sing C

Thanks @Paul Newcome. Was hoping there was a more elegant solution. Appreciate the suggestion; I'll go with that.

Nick Wilson

@Sing C @Paul Newcome

I know this thread is 2 years old, but I found a solution to this issue.

Insert a new row into your reference sheet and leave it blank. Delete any dummy rows that you used for testing/createing. Save and Exit. Your formulas with cross-sheet references should now work with "no data" in the sheet.

My hypothesis: All smartsheet sheets have 50 "blank rows" when they are created. But, I do not think those rows technically exist until data is entered. So the sheet looks like a blank file and there is nothing to reference in the cross-sheet formula. However, when you add a new row, even if that row is empty, it's still a point of reference for the fomula, becasue row actually exists even if the cells are empty.

That's my guess any ways. All I know for sure, is that I have a template with a master tracker sheet, and that sheet has multiple index/match cross-sheet refence formulas with NO dummy rows or place holders. I save the template as new and the fomulas work perfectly in the new folder. No need to update the references! Elegant.

Hope this helps.

Paul Newcome

@Nick Wilson You are still technically using a placeholder row. While the cells may be empty, that row is still considered a used row. Form entries and new rows added through copy/move row automations from other sheets will start populating on the second row, and that top row will still remaining blank.

I find I get less questions from "This is a placeholder" as opposed to just a random blank row.

Nick Wilson

@Paul Newcome

Agreed that it is still placeholder row. I have most of my team members working out of reports, so the blank row doesn't show up. Could also be useful depending on the sheet summary fomulas a person wants to use. But mostly I suppose it's a preference thing. I never liked seeing "This is a placeholder." Would much rather see a blank row.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.