-
Formula works unless there is no date and then it returns the dreaded #Invalid data type.
=IF(Helper@row = "1", "January", IF(Helper@row = "2", "February", IF(Helper@row = "3", "March", IF(Helper@row = "4", "April", IF(Helper@row = "5", "May", IF(Helper@row = "6", "June", IF(Helper@row = "7", "July", IF(Helper@row = "8", "August", IF(Helper@row = "9", "September", IF(Helper@row = "10", "October", IF(Helper@row…
-
How do we apply COUNTIFs with multiple conditions?
I Have 5 Columns namely Phase 1, Phase 2, Phase 3, Phase 4, Phase 5 I need a count of each row item (Less than 100%) with a condition that if a task is less than 100% in multiple columns like phase 3 and phase 4, it should be only counted in the first column (Phase 3 in this example)
-
Latest Revision - without pulling Revision History
Hello! I could use some help problem solving this. I have a smartsheet tracking revision number and date for about 240 rows of submittals. I'm trying to come up with a formula that will tell me the latest revision for each row and will auto-update as new revisions are added. The goal being to have a clean print out which…
-
Does not contain this or that TEXT
Hello, We currently have a formula that works to say if the variation row contains E-Gift, the cell will say E-Gift Card and if it contains Physical Card the cell will say Physical Gift Card. =IF(CONTAINS("E-Gift", Variation@row), "E-Gift Card", IF(CONTAINS("Physical Card", Variation@row), "Physical Gift Card")) This…
-
Formula to retrieve most frequent number
Hi all, I believe I'd use MODE for this in Excel but can't find an equivalent in Smartsheet. I need to build a formula to retrieve the most commonly found numbers from a column in a referenced sheet. Does anyone have a suggestion for how this can be done without MODE? For example, if I have a column in another sheet that…
-
Capture modified date and time stamp
Hello. I have an system column that records modified date and time I also have one that records when the entry was added to the Smartsheet. We have 4 hours to respond to a new entry. What I would to do is when the status of a row changes to reviewing it would record the date and time stamp then we would use a formula to…
-
Formula help for averaging data in 1 column based on criteria from 2 other columns
I need help with a formula that will average the total in 1 column based on 3 criteria of another column and 1 criteria of another column. To phrase better, I have a column of dollar amounts that I need to average based upon the sum of all values in that column. BUT, the values to sum are based upon 3 status values of…
-
Extract date as date from date and time stamp
I am importing a date and time stamp (e.g., 9/3/2022 8:36:19 AM) and need to extract the date into a date field so Smartsheet recognizes the date. I have a formula to extract the date as text (i.e., 9/3/2022) in another column, but cannot find a formula that works to convert the month and day to two digits and converts the…
-
Nest WORKDAYS from TODAY into IF formula to return correct symbol
Hello! I'm VERY new to this, but I worked out my first nested IF formula to pull symbols based on Start & End Dates. =IF([Done?]@row = 1, "Gray", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(3), "Yellow", IF([Start Date]@row <= TODAY(), "Green", "")))) Logic... "Gray" IF task marked Complete "Red" IF End…
-
Mark Duplicates In Multiselect Dropdown Contacts Column vs Employee Names List?
Ok, we're making a labor schedule, and want to be able to note when and where someone is used multiple times so we can prevent overscheduling someone and ensuring proper manpower. With the above as an example, I want to highlight a cell in [Employees on Job] (multiselect dropdown contacts) column, when one of the values in…
