CODEWITHSUNDEEP
google-sheets
D Diver
D Diver

Reputation: 25

Auto-fill a new row with dynamic formula

I have created a simple Google Form for end-users to enter their email, check-out date and return date for a loaner computer. I was able to get the formula in Column H =IF(E2=E2,MAX(TODAY()-E2,0),0) to display the Days Overdue which checks against column E (Return Date). An apps script then runs every day and sees if the Days Overdue is greater than 0, if so, it sends a reminder to turn in email.

My issue is Column H has static cells assigned, I don't want to have to go in and copy the formula to the next cell in a newly submitted row. I have added the following to the Column H header trying to auto-populate the formula to no avail.. I've been working with a few examples but can't seem to get this to work. ={"Days Overdue";ARRAYFORMULA(IF(H2:H="",,IF(E2=E2,MAX(TODAY()-E2,0),0)))}

Where Column H is the "Days Overdue" row and Column E is the Return Date row. How do I dynamically change E2, to E3, E4 etc depending on the row?

Upvotes: 1

Views: 2476

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10269

Try this in H2:

=arrayformula(if($E2:$E<>"",IF($E2:$E=$E2:$E,TODAY()-$E2:$E,0),""))

Upvotes: 1

Related Questions