-
Can a formula determine hierarchy?
I doubt this is possible but thought I'd ask anyway. I want to know if there's a way to indent a row with a formula. So if a user submits a form and indicates that it should be a child row of a specified parent row, would it be possible to have a formula in place that indents that row when certain conditions are met?…
-
Countif on filtered list or a range based on a value on another column
I use the following function to count "=COUNTIF({Cell Range 2}, 0)" numerical values from a column. However, I should only count the cell values when a cell value in the same respective row but in another column is blank. Not sure how to proceed.
-
Formula for text columns convert to sum?
Hello, I'm trying to help build a sort of prioritization scorecard into a sheet. We have 4 columns, each with possible values of (example) "A", "B" or "C", and would like to have another column that sums each based on assigning a number value to each dropdown option. So for example, the columns and their possible…
-
Incomplete work left to go
Is there a way to calculate work days "left to go" where the task is not marked complete while ignoring simultaneous tasks (tasks that have the same predecessor)? It seems like networkdays should get part of the way there but it seems like i need an "if" statement to look at complete. Is there another way to get there that…
-
IFS Statement
I have the following equation I am trying to get working: =COUNTIFS({Range 1}, =1, {Range 2}, IFERROR(YEAR(@cell), 0) = 2018, {Range 2}, IFERROR(MONTH(@cell), 0) = January, {Range 3}, OR(@Cell="", @Cell="")) The equation has worked in the past without the or statement at the end. It was once looking for one value here, now…
-
COUNTIF Formula for when a cell contains a specific word/phrase?
I'm trying to create a formula that allows me to count the number of times the word "Postcard", for example, appears in a list in a column. I am referencing a column in another sheet to do so. However, nothing I've tried works. My instinct was to try =COUNTIF({2018 Open Enrollment Feedback Range 1}, "Postcard"). However,…
-
Check Box & IF Statements
Appreciate some pointers on this if possible.. I have 5 Columns in my Smartsheet.. the first 4 are Check Boxes, the 5th is a Text/Number.. Columns as follows.. [North] [East] [South] [West] [Direction] In [Direction], I want to display text dependant on the Checked Boxes status.. it can be any of the first 4 boxes or any…
-
IF Percentage Complete = 100% return Date
This seemed simple to me before I started.. but this has me foxed..! two columns: % Complete [Text/Number field] Date Complete [Date field] What I intended is for the Date Complete cell to automatically complete with TODAY() when the user updates the "% Complete" cell is set as 100% ..what I have so far is =IF([%…
-
Formula Help Needed
I currently have 3 Columns: Column 1 "NUMBER OF UNITS", Column 2 "UNIT PRICE" & Column 3 "TOTAL PRICE". What I am trying to do is if in Column 1, the "NUMBER OF UNITS" is 600 or less, I need Column 3 "TOTAL PRICE" to default 515. However, if Column 1 "NUMBER OF UNITS" is more than 600, I need the Column 3 "TOTAL PRICE" to…
-
If Then Function
Our Smartsheet form has a “Completed” column (it is a date column) and a “Status” column (statuses from a drowndown list). We would like to somehow tell the sheet that, if the “Completed” cell is a date then change the status to “Completed” in the “Status” column. Basically when users submit a new form, the status goes in…
