Can Formulas Reference Reports?

UnaB

I'm trying to see if formulas can reference reports or just root SS. I ran a report for all late tasks assigned to all people in a department. I then wanted to use a COUNTIF formula that would sum up all the late tasks of a certain person. When I go to do that and the formula asks me to reference a sheet, I am only presented with options to link a project SS, but not an reports. Is there a way around this?

Dan Seidler

Reports look at Sheets.  You will need to create a formula column on the Sheet, then present that column on the Report.

Kamille Gaston

Dan, I believe the question was how to create a formula (in a sheet) that references a report (that potentially has already aggregated all of the desired records for the formula). 

Kamille Gaston

Following because I would also be very interested in the ability (or a workaround) to create formulas that reference reports. 

Any other way to create formulas (countifs specifically at the moment) that would count the number of times an action appears across multiple project plans?

Andrée Starå

Hi Dan and Kamille,

Please submit an Enhancement Request when you have a moment to have your vote added

A New Way to Submit Your Feature Requests

To make your Enhancement / Feature Request count, send in the form above because there isn’t a guarantee, it will be registered otherwise.

Original Post: https://community.smartsheet.com/announcement/new-way-submit-your-feature-requests

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

Hi Kamille,

There are a few ways to set it up.

A formula on each sheet and then sum those together or a separate metric sheet where you collect everything with cross-sheet formulas and then sum them together.

Would any of those options work?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

MakeItHappen

Hi Andree,

I have a similar problem. I have created a standard sheet which has 5 numerical columns and 5 text which I want to count and total (the numerical columns). I have 5 projects using the standard sheet.

To do as you suggest I would need to create a new metric sheet with the 11 columns (1 identifier; 5 text columns; and the 5 numerical columns) and then create 50 cross sheet formulas (5 rows x 10 columns) to bring it together. Each time I have a new project I will need to create a new row with another 10 cross sheet formulas.

Does this describe the workaround you are suggesting? Is there an easier way?

Regards

Andrée Starå

Hi,

Fortunately, there is an easier way. The right one depends on your specific structure and need.

• Collect all data on the same sheet and then either calculate on it or cell-link to the Metric Sheet and do it there
• Collect all data in the Sheet Summary and calculate on it there
• Collect all data on the Metric Sheet and use cross-sheet formulas and maybe VLOOKUP or an INDEX/MATCH combination.

All of the above examples are options, but I'd need to see/know more to give a better recommendation.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

MakeItHappen

Hi Andree,

Thanks for this. Your response gives me comfort that I think I am on the right track. My solution involves your points 1 and 3. My 4 step approach is a s follows, let me know if there was an easier way.

 

1. Project sheet
   1. I have created a sheet to capture the essential information about a sales opportunity; track is as it moved through the pipeline; and then if successful through the project stages. (Picture 1).
      1. I use this as standard sheet and then copy and rename for each opportunity.
   2. To the far right of the sheet, hidden from the user, I have brought all the information that I need to capture (Picture 2) in the dashboard.
      1. The sheet column names are the names of the data fields that are needed. This means I can also use the smartsheet report builder to pull together all the information from all projects into one report.
      2. The sheet column names are also repeated in a row of the sheet. This is needed for the MATCH function, discussed later
      3. Under this row I have used formulas, e.g. vlookup or equals “=”, to bring the data across, e.g. xyz1 in the picture. (Picture 2)
      4. I had to bring in excess of 70 pieces of data across and create 70+ columnsto power the dashboard
      5. This information is in the rows under the “Report” section you can see at the top of Picture 1.
         1. Incidentally I had to move this section to the top of the sheet because Smartsheet has a maximum cell limit when it comes to specifying the RangeContainingData in an INDEX function. When this section started at row 100, and I reference from row 1 to 102 and 80+ columns I hit the limit with just 4 sheets.
            
             
2. Metrics Sheet – consolidate the data (Picture 3)
   1. The data from the projects sheet is consolidate in from column 10 onwards, again this is 70+ columns
   2. I used INDEX and MATCH to bringing the data from the project sheets into a ‘metrics’ sheet. (Picture 4)
      1. I moved away from vlookup in this sheet to eliminate the issue of columns moving position out of position when new columns were inserted in the project sheet
      2. The row mention in Step 1.2.2 is repeated on this sheet to enable the MACTH function to work
      3. This is the INDEX MATCH formula (picture 4)
         1. =INDEX({RangeContaingData}, MATCH($[Opportunity Name]6, {FindRowNumber}, 0), MATCH([Opporunity Leader]$5, {ColumnNumber}, 0))
            
             
3. Metrics Sheet – Create the data to support the Dashboard charts and metrics (Picture 5)
   1. In the first 9 columns (an many rows down) of this sheet are the various tables needed for the dashboard charts and metric widgets.
   2. It is in the first 9 columns because the width of these charts is finite. The Data in item Step 2.1 just gets longer and longer.
   3. I used =SUMIFS and COUNTIFS to total the data, e.g. count how many 'Solution2' have a Objective of ‘Retention’ and were ‘Successful’ (Picture 5)
      
       
4. For New Projects
   1. I copy rename the sheet used in Step 1.
   2. In the ‘Metrics Sheet – consolidate the data’ section, (Step 2) in the next free row, I would type name of the new opportunity in the ‘Opportunity Name’ column i.e. “qwe1” in Picture 3.
   3. In the next cell to the right (i.e. ‘Opportunity Leader’ in Picture 3) I would type out the INDEX MATCH formula in full referencing the new sheet.  
   4. I would then copy this formula across to the remain 70+ column to bring across the data from the new sheet
   5. This data is picked up in the chart data section (Step 3) and as a result the Dashboard is also updated and in real time for any changes.

 

It would be so much easier if the Smartsheet formulas used in Step 3 could reference a Smartsheet report. Step 2 is essential rebuilding a Smartsheet report.

In hindsight I might have put Steps 2 and 3 on separate sheets. However it is easier to write the formula when it is all in one sheet. Referencing another sheet involved extra mouse clicks.

I don’t know enough about Control Centre to know if it could have done the above in a more streamlined manner.

Regards

 

Andrée Starå

Happy to help!

I'll take a look and get back to you if I have any tips for improvement. 

Nicely done! 

Best,

Andrée

Ramsay Zaki

This workaround works... but we often add many new projects. Having to manually reference the new Project sheets in the consolidated metric sheet isn't scalable. I need the ability for this to be scalable so that whenever a new project is added (with the appropriate underlying sheets), the rollup happens automatically.

Seems there isn't a way to automatically do this without having Control Center?

Paul Newcome

If all sheets are within the same workspace, you can reference the workspace as the source for your report and it will automatically add new sheets.

Kuldeepak Sharma

@Andrée Starå, Can you share some inputs on @Ramsay Zaki's question of making this a scalable solution? If a new project sheet is added it would in fact mean running all these steps for each one.

Before you advise me to Submit an Enhancement Request, I have done so already :)

Do you know if this improvement is on the product roadmap of Smartsheet?

Cheers, KD

Daniel Heidt

I have a similar need use a formula to check a report. We have several project sheets that each have TaskIDs. When our team reports its hours in a separate sheet, they input which TaskID they were working on and I need a formula to confirm that the TaskID is valid.

The way I'm doing this at the moment is with a formula in the hours sheet that looks for the TaskID in the gantt sheet. This solution won't scale, however, as we create more project sheets. Instead, I want to:

1) create a separate report that lists TaskIDs from all project gantt sheets (easy)

2) modify the formula in the hours sheet to check this report for the TaskID

Is there a way to do this, or some other workaround?