Is there a way to copy a formula value to a range in the row to log previous output?

Question

Current formula:

=IF(AND(N2>B16,L2>O2),SORTN({L2,N2,O2,Q2},1,0,2,FALSE),"")

Additional Information:

• above formula is in cell R2 to have output in R2-U2
• L2:Q2 are dynamic and may change one or more times a day
• SORTN was how i managed to get the output I wanted but not a requirement

Problem:

• I'd like to find a way to log historic values in the R2-U2 range by copying the R2:U2 values downward to R3:U2 and so on when new values are populated into R2:U2. Keeping all the data in descending order from newest to oldest. Is there a non-scripted solution that may be able to accomplish this?

Answer 1

That cannot be done with a spreadsheet formula, because formula results only depend on the source data you give through parameters, and will change dynamically when the source data changes. You cannot use a formula to create static values that remain the same forever to archive those values in the spreadsheet.

In other words, you will need a script. See Apps Script copy row value in History Sheet to first empty row for an example of how to do that.