-
INDEX / MATCH and HAS / CONTAINS?
I'm determining how long a person has been parked based on the parking lot zone number and the amount of money paid. I have a separate reference sheet that lays out the zone numbers, location names, and all of the possible amounts someone could have paid in this lot - this is the sheet I'm trying to reference. When I…
-
Formula Consolidation
This was originally a checkbox column to see if an estimated was within a 10% margin on either side, but now I need it to account for if it is under/over the 10%. Currently, three checkbox columns exist, with the following formulas: EST GOOD - =IF(AND([HR PER UNIT / EST]@row > [10% DOWN]@row, [HR PER UNIT / EST]@row < [10%…
-
Removing blanks from Join Collect output
I have a sheet that is pulling in data for metrics from another sheet. Using Join(Collect() I get the output I want but, it does include the blank cells from the range. I would like to not have them included…. =JOIN(COLLECT({PECR Range 6}, {PECR 2}, Region1), SUBSTITUTE([Column6]1, "-", ""))
-
My formula is getting a syntax error when I try to apply to column
Hi everyone, I am getting a syntax error when applying a formula to a column when the formula works when I use it. I am trying to use a helper column to identify unique order numbers for a sheet that will be continuously updated. =IF(COUNTIF([Original Order #]$1:[Original Order #]@row, [Original Order #]@row) = 1,…
-
Copying a value from one column to another when triggered
Hello - I have a sheet that has a project start date column and an end date column. When the project is approved, I would like those dates to be copied into another column (as "start date" - "end date"), or if necessary, a two columns (one for each of the dates). Once the dates are copied, I do not want them to update…
-
Can analyze data be used in sheet summary or dashboard
It would be great if the analyze data tool could be copied to the sheet summary or dashboard to continue to be updated as new data comes into the sheet. Is this possible?
-
Help With Automation
I'm trying to create an automation (alert someone) when a specific cell in my sheet is greater than or equal to $1,500. Right now, when I try to create an automation, my only available triggers are: When a date is reached When rows are added or changed When rows are changed When rows are added I need a trigger for "When a…
-
Sheet automation loop
I am looking to understand how to break a loop so I can fix something. We are trying to remove columns from a sheet, but the automations keep replacing them on connected sheets before we can get them deleted on the other side. Is there a way to export a configuration so we can see all of the connected sheets? Also, how can…
-
Count distinct values in a column if it falls within a date range
I am trying to run a formula in one sheet (let's call it sheet 1) to return a count of distinct values in an entire column of a different sheet (sheet 2) only if the corresponding date (also in sheet 2) falls within a date range. Sheet 1: Column [Site Receiving Transfer] is the column I would like to count the distinct…
-
Cross Sheet Reference If Cell is Not Blank
Hello - I'm trying to produce a Y/N output based on whether a cell from another sheet is not blank. If the cell in the other sheet is not blank, I want to produce a "Y", if it is, I want to produce a "N". This is the formula I put, but I'm getting an #INCORRECTARGUMENT. What am I missing here? =IF({CPR Expiration}, <>"",…
