Automatic copying of formulas to new rows in Transactions sheet

Hi

I have created custom columns which use formulas.

When new transactions are imported the formula does not populate in the new rows.

Does anyone know how to make this work in Sheets?

Any help would be appreciated.

Thanks!

Check out this article. I believe it’s what you’re after.

https://help.tillerhq.com/en/articles/2434330-automatically-fill-a-column-with-a-formula

1 Like

Hi @ptaljaard,
This should work.
What formulas are you using?

Make sure the formula is in Row 1.

I add the Category Type to my Transactions using:
=ARRAYFORMULA(IF(ISBLANK(A:A),IFERROR(1/0),VLOOKUP(C:C,Categories!A:C,3,FALSE)))

You probably need to use an ARRAYFORMULA function, rather than a manual fill down.

Thanks @jonorlin

I managed to create an arrayformula in row 1 that, when copied down, gives the desired outcome.

However, after refreshing my accounts, new rows don’t have the formula in that column?

I attach an image showing the 3 new transactions without formulas.

Any idea what I’m doing wrong?

Hi @ptaljaard,
You are not using arrayformula correctly. When you use arrayformula, you only need to use it in the top row. It will then automatically use the formula in the entire range of cells. That’s one of the great features of arrayformula.

Not all formulas can be turned into arrayformula, but IF and VLOOKUP can be.

More info here:

I think if you just use the formula in O1 and change both D1 references to the range D:D, which stands for all the rows in column D is will likely work. You probably don’t need the dollars signs in $A$2:$B$52. A2:B52 will likely work. Let us know.

Thanks @jonorlin!

That did the trick!

2 Likes

Hi, I am trying to link “static” data to the transactions from tiller. For instance, marking a work expense as “submitted”. One the new transactions load, it’s moving the transaction but not the data I’ve type in for that specifc transaction. Any suggestions would be appreciated.

Hi @will.auchincloss,
Could you give us a little more information about your workflow.
Did you create a new column for your static data? You are just putting data, not any formulas, in your custom cells? Did you give you new column a column name in Row 1.

I have several custom columns in my transactions sheet. And they move with the rest of their transaction lines.

So this should work.

Thank you for responding. I have solved the issue. I didn’t realize Tiller moves the data with the new transactions - that made my life easier.

1 Like