-
Extracting a string of text, sometimes between other text.
Hello all, I'm looking to build a formula that will return the digital object identifier (doi) string from a paper citation. The problem is, sometimes it the last piece of the citation, sometimes there is additional text at the end, and sometimes there isn't a doi to return. I've been playing around with a few formulas…
-
Having trouble with COUNTIFS.
Hi All, I'm trying to write a formula to achieve the following: count a value at row/label, but only if it has a date in the "Date Requested" column that fell within the last 30 days: this is the formula I want to add to. (This first formula works well for the total count.) =COUNTIF({Alpha-3 Code}, Label12) I've been…
-
Index/Collect with Multiple Criteria
I'm having issues with this formula. It's returning #Invalid Value. Once I can get this to work, I have 4 more criteria to add. The "Sub Dept" columns are single-select drop-down. The Assigned column and the column the formula sits in are Contact List. =INDEX(COLLECT({Assignments by Discipline Test Assigned}, {Assignments…
-
Trying to get the number of instances of a value from a column with a dropdown list property
I have a field (column) in my source sheet named [Hub/Site CLLI]@row that I need to find the number of instances contained in a drop down list named {Tinas node_comparison - All CLLIs} in the target sheet. When found I need to count the number of instances of the "NTODAANODE Ticket #" found in the target sheet. This is…
-
Activity Health
I am trying to recreate a previous formula I used and am not sure where I messed up for task health. Can someone help me find what is causing my IRERROR function to be true for rows 1 & 2? Formula Used: =IFERROR(IF(ISBLANK([% Complete]@row), "", IF(Status@row = "Complete", "Gray", IF(AND([Start Date]@row < TODAY(),…
-
Can't get remove invalid operation on this
Hi Smartsheet's team, Appreciate to help me out as i have these formulas that i am trying to add another function of showing blank if no data has been uploaded. for PR duration For PC target submission As you could see it works if the cell has a date but when it is still empty i can see this #invalid operaiton. Is there a…
-
Multiply
Is there any function similar to "Multiply" of excel?
-
Formula Help! INDEX(COLLECT)
I have two sheets and two columns i am trying to compare: 1) Finished Lot Inventory Summary Column Name: Development Column Name: Lot Number Column Name: Finished Lot Inventory Count 2) Starts Schedule Column Name: Subdivision Column Name: Lot Column Name: Notify On the 'Starts Schedule' sheet, I am trying to populate the…
-
Formula Insertion via Conditional Formatting
Hi, I've made several formulas for one of my sheets to identify task health based on several conditions, which all work fine. However, my problem is when I insert a new row I need to identify the health formula for that row based on the task indent level (parent/child/grandchild) which I don't know how to do. Has this been…
-
How would I place a formula where if a certain cell @row is blank, then do a formula?
How would I place a formula where if a certain cell @row is blank, then do a formula? Specifically, I'm trying to create a formula that, if a certain cell @row is blank, then it'll calculate the days since another cell. It has to do with permit submittals, if the permit was submitted on Jan 1, I need to know how many days…