Sorting out past data in a prep sheet and would like to get the month and week data filled in. I’ve tried to use the formulas here:
But I keep getting a #REF! error:
“Result was not automatically expanded, please insert more rows”
I made sure it’s referring to column A but still nothing. Worth noting that I have about 500 blank rows below the data, and even then I tried inserting a few more rows to no avail.
DOH! I just caught my own error. I was paying attention to the A column but not on the starting cell, which should be A2, not A1! Then after doing that, I just formatted the columns to be a date instead of a bunch of numbers. Leaving this up on the community in case anyone else encounters this issue.
Also, I noticed that the default Tiller template does not have the date data in A, so I’m wondering if it makes sense that on these help articles, the formula should be listed to work with the default Tiller sheet, not necessarily a generic prep sheet? So it would be
Hi @syada,
Another option to writing these arrayformulas to fill columns is to use brackets which create an array instead of the if row =1 formula. For example, you can do this in the top row of the column:
This assumes your Transaction dates are in column B. Also, the ,1 at the end would be for weeks starting Sunday. For weeks starting Monday, change the 1 to 2.
You might need to adjust the Week Number column format so its shows a number and not a date.
It is my understanding the Month and Week columns in the Transactions sheet are auto-generated by the feed when the transactions are added. If you don’t have any arrayformula() in the top row cell of the Month and Week columns, that would confirm this.
If that’s the case, I don’t think you have the option to set what day the week begins on. It is set to Sunday.
Also, if you enter a new row and add a transaction, it would not get a Month or Week value.
(There might have been a time when the Month and Week columns had formulas in them. I’m not 100% certain.)
If that doesn’t meet your needs, you would need to add your own custom column using a formula like the one I mentioned earlier.
On these sheets, when the new transactions come in, the system puts the correct dates in but then all the other dates below disappear and I get the reference error that says array result was not expanded because it would overwrite data in N2.
So, I need to manually delete the dates for the new transactions and then all the dates automatically reappear and are correct.
@Blake ,
Oh. I think some versions of the Transactions sheet have those Month and Week formulas.
Yes, you manually need to delete the dates you entered for the formula to generate all the dates by itself.
If you have entered ANY data in those columns, or any column that is created using ARRAYFORMULA(), it will break the sheet and display those REF errors. Even if you have just a manually entered space.
To fix it, remove any manually entered data. The error message lets you know where the problem cell is.
Generally, I’d vote in favor of not using the ARRAYFORMULA() approach unless you want data more customized than what comes in via the feeds. An ARRAYFORMULA() that recalcs on every row in a large Transactions sheet can slow performance. But… if you want a different start day, you might need to accept this performance hit.
If you have a partially filled column, I’d consider either erasing the data that is there and going the ARRAYFORMULA() approach to the bottom of the column. Or, I’d consider going with non-ARRAYFORMULA() in-cell formulas and fill down just the cells that are missing data; you could either leave these as formula-driven cells or “Paste as Values” so they don’t need to keep recalculating.
Something is broke. All I want is what is normally supposed to happen. Nothing else. So, now there is an array formula…I believe standard issue. Everything has a month and week. But this is what happens next. When the feed brings in the next transaction, it will put in he correct month and week but then that will break the array and I get the reference error. Then I go out and delete the month and week for that new transaction and all the months and dates for all the transactions come back. Then the process starts all over again when the feed brings in the next transaction. Is something wrong with the feed in that it breaks the array. This started happening recently. Let me know. Blake
OK, I just did that. I put Month and Week in the header after I deleted the array formula. Is that OK? Or leave it blank?
Those columns do not have partially populated data. The year column works fine with the array formula. Not sure why Month and Week do not. I will let you know.
You definitely want Month and Year in the header— as static text— after you remove the ARRAYFORMULA(). The Tiller Money feeds service relies on those headers exactly matching recognized header names to know where to put the data when appending to the sheet.
I figured it out. I was running all over the place. This was all I needed. B2 is the date and the formula gives me the week beginning Sunday regarding the B2 date. Blake