-
Flag duplicate titles but not if the title is blank
Hiya, I am trying to write a formula that flags entries if the content of the title column is repeated, but I need it to ignore entries that have a blank title. My current one is: =IF(COUNTIFS([Conference Title]:[Conference Title], [Conference Title]@row > 1, [Conference Title]:[Conference Title], [Conference Title]@row) =…
-
Sum Values in the Range of the Cells
Dear Colleagues, I have a list of products and their quantity. Several lines can belong to the same order. My task is to sum up all the values in the "Quantity" column if the value of the "Order Number" is the same. Which formula would you recommend I use? Order | Product | Quantity | Sum of Quantity O1 | ABC | 10 | 11 O1…
-
How best to automate moving data from one sheet to another based on certain criteria?
I am trying to reference a list of people in Smartsheet A and pull associated data based on that list into another Smartsheet B. I have people listed this way: Column1: Last name, Column2: First name, Column3: email address. Also in Smartsheet A (Column6) there is an alpha numeric designation associated with each person…
-
Index Match Formula: no longer working
This morning our main sheet (that houses all of our pertinent data) would not load and would only display a message that said "An error has occurred in Smartsheet". After a while the sheet came back but now all of my index match formulas that referenced that main sheet are no longer working. Has anyone else come across…
-
Planned to Actual RYG
Seeking help on a formula to show RYG. I was getting an unparseable error, however most recently receiving errors regarding syntax. I want show the following: If % complete is 100%, Green If today is greater than finish date AND % complete < 1, Red If % complete is < 50%, Yellow else Green I have looked at this until I'm…
-
Is there any way to enter a Formula into a Report Cell?
I have a sheet that has some weekly inputs. I've created a simplified Report View to only pull in the applicable columns from the most recent weeks/rows of data for the SME to populate. Recently, we had some data in our source information get split into two parts that our Smartsheet still only needs to report on as a…
-
Help! Multiselect Column Formula with INDEX, MATCH and HAS
Hello Smartsheet World! I am attempting to run a formula that will return contact emails depending on which departments are selected in a multi-select dropdown column. Sheet 1: Improvement Notes Smartsheet -Dept Attention column is a multi-select dropdown -Department Contacts column (in green) is a contact list that allows…
-
Help with Formula for Counting Unique Values
I created a tracking spreadsheet for our annual law school on campus events and on this spreadsheet, we list within the same column all of the attorney participants we send on campus for that law school. There's usually between 1-4 attendees per event and within each row. If there are multiple attendees for that event,…
-
Count distinct values based on other cell criteria
I have been able to figure out how to count distinct values in a column. Now I would like an additional complexity of asking the formula only to count cells if it meets the criteria in another column. If the value in the Status column is 'Live', count the distinct number values in the UniqueDiseaseID column.…
-
INDEX/DISTINCT with multiple criteria
Hello, I am trying to create a formula that will pull in a distinct text with multiple criteria. I do have a helper column. The original formula I used works in a different scenario where only one criteria is met. =INDEX(DISTINCT({CLT}), Number@row) But now I want to create one that pulls in "SegAddy" when "RTRD" box IS…
