Dynamic Sheet References?

Is it possible to build a sheet reference dynamically using a link (the sheet link or the sheet ID) that is pasted elsewhere on the row?

I am a specialized resource and use smartsheet to help prioritize my own tasks and add a bunch of extra data that I track. For my tasks that originate from customer-facing projects, my due dates come from the project timeline sheets for each project. Additionally, I may have tasks that I add on my own because they are not linked to a customer-facing project. If I paste the sheet ID/sheet link to the project timeline in each row for my tasks, can I build a sheet reference dynamically? I'm trying to pull in the due dates dynamically rather than by creating cell links one by one.

In semi-layman's terms, I'm trying to do the following in my due dates:

IF(isblank(sheet_id_column_on_current_sheet),"",INDEX(dynamically_created_sheet_reference_to_the_date_column_using_sheet_id_column_on_current_sheet,MATCH(taskID,dynamically_created_sheet_reference_to_the_taskID_column_using_sheet_id_column_on_current_sheet)))

I think in excel, it would be similar to the INDIRECT() function.

Find more posts tagged with

Accepted answers

All comments

jcmschwa

I am a resource and my tasks can come from customer-facing projects owned by a PM or internal projects owned by me. If it's customer-facing, the task comes from a project timeline sheet. Is there a way to dynamically set the reference sheet range for an Index(Match()) formula that can look at another cell where I paste the Sheet ID or the Sheet Link?

The project timeline sheets look like this, but there are multiples:

Task Name | Due Date

My sheet looks like this:

Task Name | Customer | Project Sheet Link | Due Date

In semi-layman's terms, I'm looking to do the following:

IF(isblank(Project Sheet Link),"",(INDEX(Due Date From Project Timeline Dynamically Created Using Project Sheet Link,MATCH(Task Name,Task Name From Project Timeline Dynamically Created Using Project Sheet Link)))

Leibel S

You cannot create sheet references dynamically in Smartsheet. You probably want to pull all your tasks into a report and work form there on all of them (yours and the the ones from the project sheets...)

jcmschwa

Thanks @Leibel S. I suppose, as long as I can get all the PMs on the same page, I could create a report that summarizes my tasks from their sheets as long as it's all the same template, right? Then I could make a sheet reference to the report rather than dynamically to the project timeline sheets?

Leibel S

You would not make a sheet reference at all, you would just use that report to work from...

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.