-
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…
-
Formula for a Countif in the last year
I am working on pulling historical data from another source sheet but I only want to evaluate the data within the last year. I am having problems with getting the formula to pull information. I am attempting to count completed projects that were ordered in this calendar year. The idea would be to take this concept and use…
-
Can I create a formula for the total value in a single cell on a reference sheet?
The left photo is my metrics chart, and the right side is where my data is coming from. Im trying to create a formula to pull from that specific cell, and reflect that same count onto the metrics sheet. Possible?
-
Update completed date on different sheet after form submission
Hello All, I have a form collecting construction progress updates. When a from is submitted, I would like the "Construction Report" on a separate sheet to have a completed date (equal to submission date on the form) entered automatically. We can assume that the forms will be submitted and kept in sequential order. I have…
