-
Forumla help sought so cancelled tasks are not counted as at risk
This formula surfaces 'At risk tasks' to a project rollup dashboard report. It does so based on the criteria of due date in the past and status other than complete. =IF([End Date]@row <> "", IF(AND([End Date]@row < TODAY(), Status@row <> "Complete"), 1, 0)) Question - What needs to be changed so that it does not surface…
-
Start date auto update?
Hi, everyone. I'm very new to using Smartsheet and am having some troubles regarding start/finish dates. I'm trying to find and use the correct formula/automation to change the start dates if the previous job task has run over its allocated time. For example, I use Smartsheet to schedule trades for job sites, if a tiler…
-
Mutliplying mutiple columns + multiplying mutiple columns
Smartsheet does not like multiplying 0's so I need some help. I can get around this by adding more columns in but that will be painful with 13 similar sheets! lol There has to be a way to do this without additional columns. There are times when the MU columns are all 0, but there are REP gallons used. I get an "invalid…
-
COLLECT formula year criteria confusion
Hi, I am trying to find the maximum value of values in Column A based on the year it is occurring as per dates listed in Column B. For e.g., finding the minimum value for all 2015 values in Column A. This is the formula I came up with but it is not working: =MAX(COLLECT([Column A]:[Column A], [Column B]:[Column ],…
-
Metrics Widget - Summarize from Many Sheets in a Workspace
I want to add a Metric widget to a Dashboard that totals the number of projects that I have in another workspace. Each schedule (sheet) in that workspace has standard summary fields that I use in Reporting. When a new schedule is dropped into the workspace, it is automatically picked up by all the reports that look at that…
-
INDEX/MATCH when the MATCH is a multi-select drop down column.
Hello! I have set up an Index/Match for the Secondary ID to be matched to the Sites Under Consideration. This works for the rows where there is only 1 selection in the Sites Under Consideration column. It gives a #No Match error for the rows with multiple Sites selected. I would like it to work and list all the secondary…
-
How can I get (on a separate metric sheet) the average SLA time within a given month?
I am trying to create a metric sheet to document how long response and acknowledgement times are taking per month for record keeping. I have a sheet that has an acknowledgement SLA column and a "date of request" (auto column for when the submission was created), and I'm looking to record the average for each month on a…
-
Using INDEX/MATCH to pull a date and CountIF not recognizing as a date
Hi, I'm using INDEX/MATCH to pull a date from one report into another based on the same ID number. If there is no match, an error message populates. =IFERROR(INDEX({Corporate CAPA Closed Effective Date}, MATCH([InfoCard Number]@row, {Corporate CAPA Closed InfoCard Number}, 0)), "CAPA Not Closed") Once the formula is…
-
Checkbox find duplicate in a column, do not check either box if either has a value in 2nd column
I am using a checkbox to find duplicate entries in the column name Email. I can up with this that worked, =IF(COUNTIF(Email:Email, Email@row) > 1, 1). Next, I am looking for both duplicates to not have a value in the Using Generic Account field and then check both boxes. Do not check either box if one of the two…
-
Metrics Sheet Doubling values
Hello, I am trying to solve have a cross-reference sheet problem that is doubling my values in my metric sheet. I am using =sum(children()) in my source sheet to sum the values under each parent/child row. When I reference the destination sheet for metrics it doubles the value because I think I am referencing to sum all…
