I’ve been playing with the Excel version of Tiller Money, hoping to learn how to convert my various templates to work in Excel. There are some painfully different behaviors between Excel and Google Sheets! One of my first attempts is to get the “Group” column working in the Transactions sheet. In Google Sheets, it’s easy to setup an ArrayFormua to look at the transactions “Category”, and then go find which group that’s in on the Categories sheet, and list it in the Group column. Not so in Excel. I thought I had it licked when I came up with this formula: =IFNA(INDEX(Categories[Group],MATCH([@Category],Categories[Category],0)),"")
Worked perfectly, listing the correct group for each transaction, until I imported new transactions. The formula, which I had placed in row 2, got bumped down the list as new transactions were inserted above it. I then thought, OK, I’ll put this formula in row 1 so it stays there, and either shows the column header, or the formula depending on the row: =IF(ROW()=1,"Group",IFNA(INDEX(Categories[Group],MATCH([@Category],Categories[Category],0)),""))
No such luck. You can’t have a formula in a header row unless you tell Excel it’s not a header row, in which case it will sort itself to the bottom of the list at the first opportunity.
Oof… I’m struggling through defamiliarizing myself with Excel too, @jpfieber.
These problems sound familiar and I don’t have ready answers for you. From a design perspective, we are committing to making the Categories, Transactions & Balance History sheets into tables so that the data is readily accessible— no more of those weird header lookups we use extensively in Sheets.
It’s my understanding that this decision precludes using formulas the way you had hoped to.
Let me know if you find out something different.
P.S My only hack-y thought is a view-only visualization in a new/separate sheet… but I’m not sure I understand your use scenario…
Yea, I probably don’t need the column anymore, I originally used it to simplify some AutoCat rules, but I eventually made changes that make it unnecessary, and I could probably remove it. I’m still guessing I’ll run into the same situation on a different template, but I guess I’ll tackle it when I get there!
First, is your Transactions sheet using an Excel Table?
If you started in the Foundation Template for Excel after about March 11 then by default it is set up as an Excel Table. If you started from a blank Excel workbook and filled it with the add-in and it created the Transactions, Balance History, and Categories sheet for you those aren’t Excel Tables right now.
I know that you can’t use a formula in the header row in an Excel Table, but you can add it to a column outside the Excel table area and it should work. It’s just a matter of whether the formula you’re using will fill down the column the way an arrayformula in Google Sheets would.
If the Transactions sheet in your workbook is an Excel table to go the far right and skip one column (leave a blank column) and try your formula in the header there and then see if the fill disrupts it or not.
Let us know how it goes. Excited that you’re diving into Excel
I’m using the new Foundation Template, so it’s a table. My use-case for this column has gone away, so I’m going to forget about this one, but I have a sneaking suspicion I’ll run up on the same issue on a different template. Funny how something that once seemed so mysterious and difficult (ArrayFormula in Google Sheets) now seems so simple and I wish Excel had it!
I’ve been working on converting a number of Google Sheet templates to Excel. The Array formulas are soooo slow in both platforms. Excel has dynamic spill (#) formulas now which are really simple and handy, so I’ve been avoiding any array formulas in many of my reporting tabs.
I had the same issue you have with adding formula columns in the default Transactions table in the Excel Tiller Template. I think it is because the Tiller Add-in inserts new rows at top because of having more recent transactions at the top. I would suggest they change it to go to the bottom of the Transaction table to add new transactions and simply add a line of code to sort that table by date (descending) before the refresh is done. It’s like a line or 2 of code. Anyway, my work around for this and other issues I was running into was to have a raw template file that is unaltered. Then create a separate Excel file with all of my reports and analysis. I use Power Query to link the tables from my Raw Template file and have a cleaner Transaction table with whatever columns and formulas I want in my linked file. For instance, I include a year column. One caveat with this approach is that you have to have Microsoft 365 subscription to access spilled arrays and Power Query. I am trying to come up with an autocat feature using Power Query functionality. Having some luck with it so far. Not sure I have accounted for all scenarios. Hope this is clear. Let me know if I can help or clarify.
Are you maybe working on something for the 2022 Excel Builder Challenge @brettlapierre ?
You said the #MagicWords…
“Power Query”
It’s like hearing “Power Glove”, but oh so much more powerful than that over-hyped (over-promised), under-performing (under delivered) piece. Rather than #PowerQuery and #PowerBI both of which I feel are MEGA UNDER-HYPED and OVER-DELIVERED on!
I thought 365 was required for Tiller, but I guess not.
I am actually #curious what Microsoft Money will be like, but I don’t have a personal/family subscription to try it. Do you?
Looks like Microsoft limits Power Query by what type of connector you want.
Makes sense, get people hooked, then get them on the SQL Server in the Cloud w/ 32 processors, terabytes of memory, and 32xWhatever licenses !
Connector Name
Excel workbook
Office 2016
Home & Student
Home & Business Standard
Professional Office 2016
Professional Plus
Standalone Office 2019
Home & Student
Home & Business Standard
Professional Office 2019
Professional Plus
Standalone Microsoft 365
Home
Personal
Business
Business Premium Microsoft 365
Apps for Enterprise
Checkout my GH repo, I went somewhere like you.
Dirty WIP right now, but curious what you think.
My next steps were going to be an AutoCat… join forces? I’ll be open sourcing this whole thing as long as it is cool with the Tiller Contest Rules.
You ever play with Power BI?
Checkout my other GH repo where I got deep into that for a month trying to analyze my Son’s Grade 7 progress, while the school used a poopy very-limited data, very-common-sadly, website powered by Moodle. I went down many paths but wound up discovering the Moodle REST API Docs Online and the rest is historygit log as they say…
Hey Steve, I want to submit something for the Excel challenge, but I do not have the time to get my sheet to where I want it. I figure I will submit something to community when I have it all done. I’ve been testing my PQ autocat function out quite a bit. Seems to work well. I’ve looked at Microsoft Money before and felt it lacked a few things that Tiller has. It was a while ago, so I’d have to look at it again to refresh memory.
There is so much that can be automated through PQ, which helps having to build crazy formulas. Power BI is the same thing as PQ for the most part. You learn one, you almost have the other. I have played a little with it at work. Good luck with your work. I’ve been too busy to focus on my Excel sheets for Tiller. Progress is slow.
Thanks for the reply, and much needed support as my vision blurs looking at Excel and wondering if I am doing the right thing. I am. You confirmed it. Internet confirmed it. Power BI confirmed it when I loaded my model from Excel right into it. Power Query just re-confirmed it when I was able to bundle an entire directory (and sub-directory) of files (e.g. ./data/2022-04-30/MyAccountDetails.csv), then de-duplicate that based on a natural key composed of the Date, Amount, and Description.
The raw Excalidraw source is there and can be edited through the Excalidraw website, or better yet, use Obsidian.md to view the whole GitHub repo as a vault, and it has Excalidraw and so many other wonderful things built-in… or don’t, I can get overwhelming
Thank you though, I appreciate it and please do get in touch if you feel like chatting at all. This project might actually come together for next week. At the very least, it’s creating a unique worksheet of all imported transactions, normalized with Source/Destination Header Mappings, de-duplicated from the real master Transactions Worksheet, and then all listed at the bottom for easy review and copy/paste of new records back into the master Transactions Worksheet. The de-duplication ensures that imports can be ran again and again against the same data and not bring anything in or change any existing records (hopefully).
!