-
Create Hierarchy Relationship using WBS and Formula?
Hi, I believe this may be a bit of a stretch but I am wondering if it is possible to automatically create the Child/Parent relationship utilizing the numbers or periods in a WBS # column as a way to automatically create the indention. I have built a pretty good process using Smartsheets data uploader to import external…
-
Remove #NO MATCH return from VLOOKUP query
Is there a way to make a cell blank if there isn't a match? I know I can do an IF statement where IF the value I'm trying to match doesn't appear in the table at all, do nothing, otherwise VLOOKUP. But seems like that may slow the sheet down because it's looking through that table twice, so doing twice the work. Is that…
-
Showing Only the Parent Rows in a Dashboard Chart
Hi - I have created a dashboard to show pie charts of my data. My problem is that the pie chart wants to include every single row as a data point, I only want to show the parent rows and not the children. I used the formula "=Count(Ancestors(row))". This gives me a 0,1,or 2 based on how many ancestors the row has. But I'm…
-
Troubleshooting formula and or row
I created a formula, but have problems. The formula is ........ =IF([Status Date]@row >= Today@row - 14, "Red", "Green") ....... The formula work fine in some rows, but not all. I'm puzzled as to why this is the case. Does the master sheet need to be purged for underlying errors of some kind. I can't duplicate the error…
-
Formula takes a few seconds to populate after the sheet is opened
What could be the root cause for a formula not populating until a few seconds after opening a worksheet? I built a formula off of a contact cell that references a roster to show an individuals manager but unlike all the other formulas in the sheet, this one takes a few seconds to calculate, it is throwing off my reports as…
-
Red, Yellow, Green
Formulas are not my thing. Can someone help me with a formula to display the following: If % complete column is 100% turn the ball green in the status column If % complete column is less then 100% but greater then 80 turn the ball yellow in the status column If % complete column is less then 80% turn the ball red in the…
-
nested ifs with greater than less than- #unparseable
Wrestled with this enough. Need to call in a friend. Can anyone figure out what is wrong with my formula? =IF([Last review date]1> 365), "Red", IF(AND([Last review date]1 < 365, [Last review date]1 > 275), "Yellow", IF(AND([Last review date]1 < 275, [Last review date]1 > 0), "Green", "")))
-
SUMIFS with multiple matches
I am trying to create a Smartsheet SUMIFS formula where if the value in a cell in column 1 is not blank, and the value in a cell in column 2 is US Commercial, and the value in a cell in column 3 is 2019 and the value in a cell in column 4 is 4.. I.e. for any rows where there is a match for - deal id is not blank, where…
-
Counting number of times Name appears in multiple contact column
I have a column set up as a multiple contact to capture all authors on a journal publication. I would like to use a formula that will count the total number of times a specific name appears in then column. I have tried =COUNTIF({ALL AUTHORS},"John Doe"), but I know it does not yield all of the times the name appears. I…
-
Counts with status and Date
Hi, I'm new to Smartsheet and I'm having some issues with one of my formulas. I want to get a count of everything where the Status = Completed and then the following: * Due Date = Completed Date * Due Date > Completed Date * Due Date < Completed Date