Options
UnaB ✭
11/20/18 edited 12/09/19 in Formulas and Functions
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 usea COUNTIF formulathat 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?
Tags:
- Sheets
- Formulas
- Reports
- Community Platform
- Project Management
- Cell linking
2 · Share on FacebookShare on Twitter
Dan Seidler ✭✭
11/21/18
Options
Reports look at Sheets. You will need to create a formula column on the Sheet, then present that column on the Report.
0 · Share on FacebookShare on Twitter
Kamille Gaston ✭✭✭
06/05/19
Options
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).
2 · Share on FacebookShare on Twitter
Kamille Gaston ✭✭✭
06/05/19
Options
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?
1 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
06/06/19
Options
Hi Dan and Kamille,
Please submit an Enhancement Request when you have a moment tohave 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 willbe registeredotherwise.
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
0 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
06/06/19
Options
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
0 · Share on FacebookShare on Twitter
MakeItHappen ✭✭✭✭
10/23/19
Options
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 11columns (1 identifier; 5 text columns; and the 5numerical columns) and thencreate 50cross 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 10cross sheet formulas.
Does this describe the workaround you are suggesting? Is there an easier way?
Regards
1 · Share on FacebookShare on Twitter
Hi,
Fortunately, there is an easier way. The right one depends on your specific structure and need.
- Collect all data onthe 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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
0 · Share on FacebookShare on Twitter
MakeItHappen ✭✭✭✭
10/30/19
Options
Hi Andree,
Thanks for this. Your responsegives me comfort that I think I am on the right track. My solution involves your points 1 and 3.My 4step approach is a s follows, let me know if there was an easier way.
- Project sheet
- 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).
- I use this as standard sheet and then copy and rename for each opportunity.
- 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.
- 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.
- The sheet column names are also repeated in a row of the sheet. This is needed for the MATCH function, discussed later
- 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)
- I had to bring in excess of 70 pieces of data across and create 70+ columnsto power the dashboard
- This information is in the rows under the “Report” section you can see at the top of Picture 1.
- IncidentallyI 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.
- 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).
- Metrics Sheet – consolidate the data (Picture 3)
- The data from the projects sheet is consolidate in from column 10 onwards, again this is 70+ columns
- I used INDEX and MATCH to bringing the data from the project sheets into a ‘metrics’ sheet. (Picture 4)
- 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
- The row mention in Step 1.2.2is repeated on this sheet to enable the MACTH function to work
- This is the INDEX MATCH formula (picture 4)
- =INDEX({RangeContaingData}, MATCH($[Opportunity Name]6, {FindRowNumber}, 0), MATCH([Opporunity Leader]$5, {ColumnNumber}, 0))
- Metrics Sheet – Create the data to support the Dashboard charts and metrics (Picture 5)
- In the first 9 columns (an many rows down) of this sheet are the various tables needed for the dashboard charts and metric widgets.
- It is in the first 9 columns because the width of these charts is finite. The Data in item Step 2.1just gets longer and longer.
- 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)
- For New Projects
- I copy rename the sheet used in Step 1.
- 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.
- 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.
- I would then copy this formula across to the remain 70+ column to bring across the data from the new sheet
- This data is picked up in the chart data section (Step 3) and as a result the Dashboard is also updated andin real time for any changes.
It would be somuch 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
1 · Share on FacebookShare on Twitter
- Project sheet
Andrée Starå ✭✭✭✭✭✭
10/30/19
Options
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
0 · Share on FacebookShare on Twitter
Ramsay Zaki ✭✭✭✭✭✭
03/23/20
Options
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?
0 · Share on FacebookShare on Twitter
Paul Newcome ✭✭✭✭✭✭
03/23/20
Options
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.
0 · Share on FacebookShare on Twitter
Kuldeepak Sharma ✭✭
02/12/21
Options
@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
0 · Share on FacebookShare on Twitter
Daniel Heidt ✭
07/22/21
Options
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?
0 · Share on FacebookShare on Twitter
Help Article Resources
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- 59K Get Help
- 85 Global Discussions
- 87 Industry Talk
- 396 Announcements
- 11 Community Corner Newsletter
- 68 Brandfolder
- 120 Just for fun
- 41 Community Job Board
- 22 Member Spotlight
- 1 SmartStories
- 235 Events
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!