I could do with your help on a tricky problem I’ve come across. It’s not really essential to my app but it’s a nice feature!
Essentially, I have a log where I record all transactions, and in a helper column I have a “Duplicate Check” formula to stop me entering the same transaction twice. It uses COUNTIFS. The issue I have is that if I put this formula in place, my transactions get added on as new rows below the bottom value of the helper column, meaning my helper column isn’t very helpful!
I have created a workaround where when a form is submitted it also submits a hidden column value that is the formula that I want to put in the helper column. Should work fine, except the only issue is that glide puts a ’ before the = of my formula, meaning the formula doesn’t actually calculate and is entered as text! Is there any way around this, or am I going to have to just do without?
Hi @H_Whelan, I think that what you need is to use ARRAY CONSTRAINT and ARRAY FORMULA to apply the formula you want to a number of rows. Almost all my apps use that, you can do a simple Google Search to understand what I’m talking abou but one usage example is:
This checks if A2 to A (represents the last row of column A without specifying a number) is empty, if TRUE do nothing else, the row will be equal B2 to B. This formula will applied for 3650 rows and 1 column.
I second the use of arrayformula, but the problem is, I don’t think it works with COUNTIFS. Is there any way you can join values together and use a COUNTIF instead with a single comparison check? That way you can use an arrayformula. Just be sure to delete all empty rows, otherwise new data will be added to a new row at the very bottom of the sheet.