-
Formula Assistance for Returning a Due Date
Hello, I feel like I'm almost there on the formula, but can't figure out why WORKDAY is causing a #unparseable error. Function of the formula: If urgent flag is marked on the row I want the due date to be due on the same date that was by the 'Date Created' column type. If the flag is not marked, I want the formula to…
-
Calculating Age Monthly
I have a smartsheet with many events that often span several months. I want to calculate the average age of relevant events for each month. For example, for the month of March, I want it to calculate the age of all events that were closed within the month of March in addition to the average age (as of the end of March) of…
-
Date Formula Issues, Metric sheet
I am very new to smartsheet. What formula would I use to count how many times the year 2021 appears in the blue (parent) rows? I also would like to make a formula for a metric sheet that automatically updates as I add and delete years from my main sheet. What formula would I use for that part as well? I've tried a few…
-
Does anything in range A appear in column B?
I am trying to set up a formula that lets me know if any of the Project Tittles in one sheet appear in any comments on a separate sheet. When the function should return "True", I get "#invalid data type" =IF(CONTAINS({Project Tittle Range 1}, [Comments]:[Comments]), "True", "False")
-
How to make widget that counts year in date column?
I am making a dashboard, and I would like to make the specific year's number change as I add/ delete sections in my sheet. (right now each number is manually typed from a report I made). I have each item filtered by year, and then each item has its own specific date on the sheet. What formula can I use to do this on a…
-
Pivot Table Summaries Getting #REF Error?
I have a few pivot tables set to update weekly, and seem to be refreshing the actual numbers just fine. However, I have some summary functions set up that have totally stopped working, instead giving a #REF Error: I'm a little surprised, as it worked perfectly (with real cell references, not #REFs) when I originally set it…
-
How to search cell string for text keyword presence
I'm using Jira Connector, and the Jira Label field in the source Jira project can contain multiple values, which get populated in Smartsheet Label column as comma separated strings for each Jira issue row. I am looking for guidance on how to write a formula to search the cell string for a specific keyword, to take…
-
Copying rows to new sheets based on multiple selections in one column
Hello, new to Smartsheet so might not use the correct language, but hopefully someone understands what I need! In my company we host CPD sessions where suppliers will come in for an hour over lunch and teach us something new. We need to keep a record of these sessions and who attended them. I have set up a sheet where I…
-
Convert Quarters to Date
I have a list of dates that are Quarter/year (in a format like Q4 2031, Q1 2038, etc.) and would like to use a formula to show the last date in the quarter. So: Q1 2031 = 03/31/2031 Q2 2027 = 06/30/2027 Q3 2033 = 09/30/2033 Q4 2035 = 12/31/2035 I would appreciate help creating the appropriate statement!
-
Help with a Sheet Summary Formula Error
I have a simple sheet summary formula that keeps returning "Invalid Operation". I am wanting to count the number of instances where the Potential Loss column is over $500. I've tried every variation of "500" I can think of (500.00, $500, "500.00"), but it's still returning an error. Any thoughts on what's wrong with my…
