Dynamic lookup for Project Master Rollup

I would like to create a master project rollup to track projects, and do not want to set up cell linking every time a new project is added. To this end can we create a dynamic lookup so all the user has to do is type the name of the sheet referencing the new project into the master rollup?

Find more posts tagged with

Formulas

Cell linking

Enhancement Request

Comments

Chris McKay

Hi Luke,

Th short answer is no.

The only way to reference other sheets is via cross-sheet references and these are statically named ranges.

There are definitely hack and slash methods to get around it, but it would require some pretty serious setup, would be prone to breaking and will require a degree of admin overhead that would have you questioning your sanity.

The only way to achieve what you want is to use Smartsheet Control Center, however this will require significant investment in time and money (professional services plus ongoing licensing).

Kind regards,

Chris McKay

Daniel Stein

Luke,

It's pretty common for customers to use a Report for the type of roll-up scenario you mentioned. For example, you could create a report that looks at all sheets in a particular workspace. On each project sheet in the workspace, you can have an "Include in report" checkbox column. Then you can check any roll-up rows in each sheet that you want to appear in the Roll-up report. If you like, you could check just a single summary row (like the top row) in each project sheet.

Best regards,

Daniel

L_123

i've considered a report, my issue is I can't add formulas on a report to check the data from multiple sheets. Example: report number of projects opened and closed during the fiscal year.

Thanks, I'll see if I can find a way around it. Has anyone had any success using zapier for this purpose? Maybe using a row identifier to tell zapier to grab the master row on a new sheet?

L_123

Thanks. I do wish that was an option in smartsheet as one of the main toted features is communication of information. I'm confident there is a way to do it if I could program in API, but it shouldn't take a programmer to utilize smartsheet in this way.

Richard Rymill SBP

Luke

this is something that is a popular requirement, we do all the time and is quit easy to achieve with a minimal overhead. I presume your reasons for not cell linking are it is fiddly? Doesnt have to be. The elements of this solution are...

Using a combination of the latest Dashboard for KPI/ metrics,

Reports that will show the Top Row KPIs of each project as you add them to the workspace.

Plus a portfolio sheet which lists the KPis of each project as a Row, then within that sheet use Formulas to collect the stats in that Portfolio sheet and display them on your Dashboard.

This is the simple yet very effective method.

in your Project sheet Master, Create columns to the right of your normal actions columns ( then hide them is you wish?) . In each column, top row, use Formulas to sum the KPIs in that sheet eg. 5 KPI metrics 5 columns side by side in your master (template sheet) then it is simple to drag across those 5 cells and cell link them to your Portfolio sheet, which using Hierarchy will always sum it's children. Those stats can go to your Dashboard KPIs and create Graphs too.

There is no big overhead here.

Actions are... once you have a Blank Master Project sheet, save as new in the correct workspace, input the new project detail, Open your Portfolio sheet, next spare row, LINK from Cells in other sheet>> choose new project sheet and Row /5 columns.

You now have a new project sheet that took < 5 mins to set up.

A report that auto lists all your Projects showing Important detail.

A dashboard that shows your High level Data

A portfolio sheet that does the high level analysis and drives your dashboard and graphs.

We have used this layout with numerous clients and can demonstrate it to you or anyone else if you wish.

Cant beat a simple and low cost solution! KISS!

Hope that helps.

RichardR

L_123

Very good idea. I had to read it several times before I caught on, but that is a great solution, one that should be a template for help on the smartsheet website.

Thank you very much.

Richard Rymill SBP

Thanks for your kind words Luke. it was one of my colleagues James Rezin that first came up with this simple yet very effective solution for a health service client managing lots of hospitals progress.

We love it when simple solutions solve big problems for us.

Richardr

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…