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:


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


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

  • 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
    Leibel S ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!