-
Is there a way to create a list out of joined string in a cell?
I have Sheet A, were I have a column with Task Description that has parents. I created another column where I put the parents corresponding to the child. Example: Parent ID .........|........Task Description [blankcell]........|........Parent 1 [Parent 1].........|............Task 1 [Parent 1].........|............Task 2…
-
Count Distinct Values in a Column
I have a serial # column that I am trying to create a "helper" column to verify that the serial # entered is unique - to avoid/flag duplicate entries with conditional formatting later. I have tried this formula in the blue highlighted cell pictured below: =COUNTM(DISTINCT(([Computer Serial # (Parent Asset)]:[Computer…
-
INDEX/Match Problem Across Sheets
Here is my problem: I'm trying to compare dates on sheet A to a list of dates on Sheet B and if the dates match, fill in a number on sheet A. If I move the data over to sheet A and use this formula works fine: =INDEX([Column9]:[Column9], MATCH([Column4]@row, [Column8]:[Column8], 0)) When I use a cross sheet reference I get…
-
Long IF Statement Help
Hello, I am attempting to create an IF statement on a Red, Yellow, Green, Grey icon text type with multiple factors. Below please find the inputs listed. If new entry then Gray (COMPLETED) =IF([Form Submission Date]@row = TODAY(), "Gray") If hard deadline has passed and nurse intake has passed then Red (COMPLETED, but…
-
Sheet Formula_ Heath symbol
Hello everyone, I know I am close, but looking for some help. I am trying to create a formula to calculate If start date is less than today, health symbol is Green If start (maybe should be end date) is within (xx) days, health symbol turns yellow If start date (maybe should be end date) is greater than today, turns Red If…
-
Tracking "Time at Current Stage"
Hi Guys I have a question regards tracking comments/ current stage of ticket items We have an export that is added via the Data Uploader which contains "Last Week" & "This Week" fields. We are hoping to expand the range by which we can track how long an item has been at a certain stage. The idea is we add the file via the…
-
vLOOKUP on Multiple Dropdowns Pulling, Calculating Totals
Hoping someone can help! I am have created a multiple dropdown list in one column (a), and a VLOOKUP function using this data in a separate column (b) to pull through pricing from another sheet. I am having some issues when column A has more than one option selected. How can I create a formula that checks all options…
-
Convert Text to Date Format
I am trying to copy a text column written out as "Thursday 07/29/2021" into another cell with a date format "07/29/21" in order to use that cell as a reference. I have tried the VALUE formula but have not been able to get it to work... Thanks for you help!
-
Apostrophe in formula
Aim: I am running a helpdesk in Smartsheet and am trying to autopopulate 'ticket closed' fields for Year, Month, and Week according to a 'date closed' field. E.g. YYYY column: =IFERROR(YEAR([Date Closed]@row), "TBC"). Once a ticket is closed, the YYYY cell should display '2021'. Context: The sheet is a local sheet (for a…
-
Assigning a value to a field
Hello. Could use some help / suggestions on the following formula. =IF(AND([Date Completed]@row = " ", [Due Date]@row >= TODAY()), [Past Due (Y/N)]@row = "True", [Past Due (Y/N)]@row = "False") I have a Due Date field, a Past Due flag field, and a Date Completed field. I would like this process to set the Past Due (Y/N)…