-
How to use automation to copy only certain rows to another sheet?
I have large rows of dataset in a sheet. I am looking for a solution that can copy specific rows to another sheet based on certain criteria. I used the automation template for copying rows, however, on fulfilling the condition specified, the automation all rows in the primary sheet to the secondary sheet. Is there a way I…
-
Extract text from a file string
Hello! Simple question (I hope). I have a list of document locations - that look like this: _Central Documents/Training/Investigators/Rhodes, Meredith/CV I would like to parse 'Investigators' into a staff role column, 'Rhodes, Meredith' into a contact column, and 'CV' into a document type column. What is the basic formula…
-
Using the holiday parameter in workday function
=IF(Runs@row = "Daily", WORKDAY([Completed Date]@row, 1, {Holidays Range 1}), IF(Runs@row = "weekly", WORKDAY([Completed Date]@row - (WEEKDAY([Completed Date]@row) - 2), 5, {Holidays Range 1}), IF(Runs@row = "Monthly", WORKDAY(DATE(YEAR([Completed Date]@row), MONTH([Completed Date]@row) + 1, 1) - 1, 1, {Holidays Range…
-
New! TIME function for formulas
Hey Community, Some of you may have noticed a new function in our Functions List last week… Introducing the TIME Function! This returns the time of day in 12- or 24-hour format from a given set of values. Syntax TIME(time_value, [ format ], [ precision ]) time_value — The value to create time with. Must be provided as a…
-
Date formula
Hello! I currently have 2 columns on my sheet, one where I enter date processed and date expired which has a formula to calculate 1 year expiration from date processed. I have been asked that no matter what date I process, it needs to expire the last date of that month. So for example, date processed = 9/5/23 date expired…
-
Convert to Column Formula Error
I'm trying to make the below formula a column formula. If I copy and paste the formula into cells, it works as expected. =IF(OR([Status]1 = "Complete", [Status]1 = ""), "", "Yes") Two issues, though: 1) The [Status]1 brackets are being automatically removed and reformatted as Status1 2) When I try to convert to a column…
-
Count & Sum Distinct Values based on Criteria | COUNT(DISTINCT(COLLECT(...) | Returning 1 ???
In a ROLLUP sheet, referencing a source sheet, I'm trying to count (and sum) DISTINCT values that meet multiple criteria. My formula keeps returning "1", when in the shown scenario below has "80" distinct values. Hoping I'm just missing something simple. Rollup sheet purpose = count the distinct values rented within…
-
I am identifying the duplicates in the sheet. What wrong with this query?
=RANKEQ([Request ID]@row, COLLECT([Request ID]:[Request ID], [Item Number (SUPC)]:[Item Number (SUPC)], [Item Number (SUPC)]@row, Duplicates:Duplicates, 1), 1)
-
Using the RYG "Yes, Hold, No Decision" and want to calculate based on staff assigned to
Our sheet has a status column with the RYG "Yes, Hold, No Decision" icons. There is another column labled "Staff" that lists which team the tasks is assigned to. I'd like to, in the Sheet Summary, Have a count and/or percentage of each yes, hold, or no decision for each type of staff selection available in that columns…
-
Formula IFS return values from other columns
Hi, i am trying to set a new column that should return EAS or ETS text depending of what it is in column named "ETS", but i want to add another if that return a value from column "Business Dropdown" if the column ETS is blank. This is the formula that i wrote now very simple and it is running well, but it is missing the…
