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.
Any advice?
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
=arrayformula(if(row($B2:$B)=1,"Month",iferror(date(year($B2:$B),month($B2:$B),1))))
Instead of references to $A1:$A every time.
Just a thought!
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:
={"Month";arrayformula(iferror(date(year($B2:$B),month($B2:$B),1)))}
This will put “Month” in the first row and then the arrayformula starts in row 2 to the bottom. You need to use a closed } and the end of the formula.
I think this formula is a little cleaner and easier to change the Column header name.
Some earlier Tiller transactions sheets had the Date in Column A. I think that is why the example uses Column A.
Jon
Jon,
I am having issues too. It is in my transactions sheet. What should I use for Month and Week beginning Sunday and Week beginning Monday?
Thanks,
Blake
Hi @Blake,
You could use the WEEKNUM() function to create a week number column based on whatever week starting day you want. See the options here:
https://support.google.com/docs/answer/3294949?hl=en
The formula would be something like this:
={“Week Number”;arrayformula(iferror(weeknum($B2:$B,1)))}
This assumes your Transaction dates are in column B. Also, the
,1at 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.
Does that work for you?
Jon
Jon,
What about Month? Also, don’t I need an array formula since this is the main Month and Week column which is standard issue in the Transactions tab?
Blake
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.
Jon
Jon,
I have 2 sheets that have Month and Week in row 1 and 2 sheets with these array formulae in row 1.
=arrayformula(if(row($B1:$B)=1,“Month”,iferror(date(year($B1:$B),month($B1:$B),1))))
=ARRAYFORMULA(IF(ISBLANK($B:$B), “”, IF($B:$B <> “Date”, $B:$B-WEEKDAY($B:$B,3), “Week”)))
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.
How do I fix this?
Blake
@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.
Let us know if that worked.
Jon
This is my problem. How do we fix it?
Some quick thoughts, @Blake…
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.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
The ARRAYFORMULA() trying to populate the entire column from the header is not compatible with partially populated data, @Blake.
I would recommend:
ARRAYFORMULA() from the header.Let me know if I’m misunderstanding the nature of the problem or your desired solution.
Randy
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.
Blake
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.
That is what I thought. Blake
=B2-WEEKDAY(B2,2)
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