Formula-driven Column in Transactions Sheet - Apply to New Rows?

Google Sheets
1

I added an extra column in the Transactions sheet, and it contains a formular. After I run AutoCad and filled the sheet with new records, the extra column for those new records doesn’t have the formula, and I have to manually copy the formula to those new records/rows.

More details: What I am trying to do is … The new column is called Fiscal Year … the formular is: if the category is in a certain list, the fiscal year is from Oct to Oct; otherwise, it’s Jan to Jan …

Is there a way to automate this?

2

You might try using an ARRAYFORMULA() based in the column header that fills all rows. It might look something like this.

3

I don’t get it … For existing rows in Transactions sheet, I have a formular to calculate the value for this extra column; but when new rows are filled into the sheet, the formular is NOT copied to the new rows

4

The transaction importer creates a new row, fills in the info it has, and then sorts the row. It doesn’t know anything about your formula, so it’s not included in these new rows. Using an ARRAYFORMULA, as @randy suggests gets around this by automatically applying the formula to every cell in the column, even new ones.

1 Like
5

Use the same wrapper as the formula I referenced but replace the REGEXREPLACE() with your your formula.

6

Thank you everyone, I got it this time :slight_smile:

The formular on the header cell for that column is (Fiscal Year from Oct to Oct) …

={"Fiscal Year";ARRAYFORMULA(if(month(B2:B)<10, Year(B2:B), Year(B2:B) +1))}

Have a nice day.

1 Like
7

Well done, @walter.ren!
Thanks for explaining how the feeds update works, @jpfieber.