-
Check for overlapping date ranges between sub tasks
Hi, I am looking for a formula that can check for overlapping date ranges between of the sub tasks on a project. In the screenshot below the project has several sub tasks and I need a formula that will identify any overlap between the dates on the tasks. I would like to prevent the use of references to cell numbers because…
-
Remotely fill in a new row on another sheet.
Hello, I have two separate sheets that need to be connected. One is an inventory sheet of all raw materials in stock, and the other is a record of all raw materials delivered. If we receive a delivery of a type of raw material we already have some in stock of, the quantity of that material is simply updated in the…
-
extract part of a cell's text
Hello, New smartsheet user here. I have cells that contain dimensions of objects. A cylinder, for example, looks like this: Ø120*12. I need a way to extract just the text to the right of the *, in this case 12, to another cell. Another example: Ø18*1000 would return 1000. How do you do this? Thanks!
-
Count and compare date columns in 'form' based worksheet
I would like to know how I can count number of activities (rows) which are completed ('finish' date column) at or before 'planned finish' date. In the same formula it should only be counted when 'complete' column is ticked off and empty cells should not be counted. Note that this is a 'form based' worksheet, meaning rows…
-
Traffic Lights/cell colour based on corresponding numeric value
Hi, can anyone please help? I want to set up an alert system in SmartSheet that turns a cell green (and labelled 'on track') if a corresponding cell is >90%, yellow ('needs work') if 70%-89%, orange ('at risk') if 40-69%, red ('behind') if 2-39%, and blue ('not started') if <2%.
-
Networkdays and #invalid data error
Hoping you can help with the below formula. I need to return zero and can not get the iferror formula to work. current formula is =NETWORKDAYS([Agreement Effective Date]4, [Actual Go Live]4). Screenshot included for reference. Thanks, Heather
-
IF formula or Nested IF
I'm trying to create a formula using the Flag symbol and a % Allocated to Project column. I would like the flag to raise if the % Allocated to Project column is 50% or less. If greater than 50% or Blank I would like the flag to stay down. I thought I could accomplish this with =IF([% Allocated to Project]@row< 0.5, 1, 0)…
-
Countifs with AND/OR
Hi There I have a Countifs that is counting the number of projects that are healthy (Green), marked as "prioritized", are "in progress" and match a particular category. This works great, except I'm trying to expand this to look for "in progress" projects OR "not started" projects, but maintain all the other AND criteria..…
-
Filtering on dashboards?
I'm new to smartsheet and trialing it at the moment. I have a question, I hope you can help me with: I have a portfolio of companies in a main sheet and their debt issues, and related stuff in several sheets. Rows are companies/debt issues, columns are details of these. I want to collate all this into the dashboard used…
-
Countif multiple criteria
Hi, I would like to extract the number of tasks completed by a certain team using different sheet Project team IT Compleated Not Started Inprogress Project team ERP Compleated Not Started Inprogress