Dynamic Sheet References?

jcmschwa

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.

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...