-
Formula to return Monday from a week a date was on
Hello, I'm looking to put together a formula that will pull the Monday a date was on so I can then pull things that were 1 week ago, 2 weeks ago, etc. to build out a report for weekly hours done by members of a team. I've tried some other methods to try and do this but they broke on the year change because Smartsheet just…
-
Need Help With Colored Health Status Formula
Hello, I have a symbol column that automatically will assign a color (currently either red, yellow, or green) based on a due date in a column called "NEED ON SITE DATE". The formula looks at the "Fab Status % Complete (Needs Real Numbers)" column and will assign it a green dot if the percent complete is 100%(In this case…
-
Index Collect or Lookup Formula Help
Good Afternoon All, Currently having trouble with an index collect formula between 2 SmartSheets. On Sheet A there are 50 Unique ID's that correspond to 50 Creative Names on Sheet A. On Sheet B I am trying to bring those 50 unique Id's over by matching back to the 50 Creative Names on Sheet A. So my formula is as such:…
-
Cross-Sheet MATCH INDEX ERRORS
I am running in circles trying to solve this issue - seemingly, in using MATCH() on an external cell reference, the value it returns caps itself at 14 even though data goes out to ~30. While I end up using this base function in much larger equations, the screenshots explain the basis of the issue: Here is the Cell…
-
formula for multiple criteria
i am looking for a formula to track progress / multiple statuses migrated = 100% decommissioned= 100% In Progress = 50% Incomplete - reporting error = 0% Not Started = 0% Failed in PROD = 50% Decom Candidate - 0% what formula can i use to get a update in a column if any these statuses reflect
-
COUNTIF with OR
I am trying to count all instances of a name where the corresponding status is Open or Tracking. My formula is =COUNTIFS({Coordination Log - Autumn Willow Range 1}, "Chris Rush", {Coordination Log - Autumn Willow Range 2}, OR(@cell = "Open", @cell = "Tracking")) In the last portion where the OR comes into play, I initially…
-
Pulling cell data based on date and name
Hello, Could someone help me with a bit of a pickle I can't get around? I have a sheet with multiple dates and names and their daily progress. The dates are pre-populated for the entire month with everyone's names assigned for each day. I've been trying to figure out how I can extract the status percentage for a specific…
-
What formula to use to create bar chart with percentages instead of numbers
Hi All Could you please advice what formula to use in metrics sheet to convert numeric data into %. Based on this calculation I would like to create a bar chart that shows what % of all projects completed were affected by some issues at particular stages of project execution. Please see the example. At this moment we have…
-
Formula: Adding Time
"Hi there, Just to provide some context, I'm currently working on creating a metrics sheet, and I'm looking to aggregate all the hours based on the Legal Team Member." Formula's I have tried: =HOURS(SUMIFS({SLA Hours}, {Legal Team Member}, "John Doe") * 24) =TEXT(INT(SUMIFS({SLA Hours}, {Legal Team Member}, "John Doe") *…
-
Child Parent Formula Help
I want my formula to look at the child count column and if it is great than one to run a sum of the children rows and if it has 0 children to run a lookup SUMIFS formula: I was hoping it would work like this but I keep getting errors. =IF([Children Count]@row > 0, SUM(Children()), SUMIFS({Curo.Points}, {Curo.Category},…
