I know that changing column headers on template sheets is usually something you want to avoid, as they’re used to build dynamic connections elsewhere in the workbook. On the hidden Accounts tab, there are essentially two sections, the accounts part (Columns F-Q), and the override section (Columns A-D). These two sections share two identical column names, Account and Group.
I want to use the same dynamic trick to pull the column index and number back for Accounts on a few of my custom sheets. The issue, however, is that I want the second ‘Account’ instance that maps to column H (index 8), and the second ‘Group’ instance that maps to column P (index 16). If I use a MATCH to pull back the Index, it will return 1 for ‘Account’ and 3 for ‘Group’, as they are the first instances of those strings.
Is it safe to rename the first instances of these to instead be ‘Account override’ and ‘Set group’? This will make all column names unique, as well as provide a little more context to what the columns actually do. I’ve ran through my sheets, and I don’t see these columns being dynamically referenced anywhere. After changing the columns to the above, I do not see anything off from a quick glance over all my tabs.
I believe it’s fairly safe, but I wanted to ask. Also, if anybody knows of a way to return the second index of a matched instance instead of the first, that would also work, and would not require any changes to the accounts page at all. I was hoping to solve this in the best way possible so that I can provide my solution to the community.
You’ll have to change this formula in all sheets that are trying to match column letters with ACCOUNTS.
This doesn’t find the second instance, it just starts at F instead of A.
I’m not sure there is an easy way to find the second occurrence starting at A.
it would be better if Tiller would redesign the Accounts sheet to not have duplicate column names. But that would require revamping ALL the templates that use Accounts!
Thanks for the reply! That is exactly the workaround I had worked out, as well. Changing it to start at F changes the index from 8 to 3. So I added 5 and all is well. This solution will work if nobody moves the columns in the Accounts page to be before E (which shouldn’t happen anyway, but you never know).
I agree that the optimal solution is to have no duplicate column names. And when I change the column names on my sheet, nothing in the Foundation template breaks, nor anything in the other solutions I have installed (Savings & Debt, Savings Budget, Debt Planner, Tags report, budget journal).
I don’t know if there’s even appetite for my solution in the community, but I wanted to throw it out there just in case, and I’m trying to make it as friendly as I can. I don’t want to have to make people rename those columns for it to work.
Looks like you are deep into the sausage-making, @jemmoa7 and @1Email2RuleThemAll. That duplicated header issue is one of my biggest regrets in the rearchitecture of our Foundation Template years ago, which otherwise has had a great run.
We use the trick @jemmoa7 captures when looking for data in the sheet. I don’t think there are any downstream sheets that pull data from the left side. Any data manually input into the left side is pulled into the formula-driven summary on the right. All downstream sheets pull data from the summary.
Thanks for the confirmation, @randy! I just started to use Tiller about two months ago, and I absolutely love it. I’m a BI Developer with a background in programming/app dev. I built my own finance tracker years ago in Google Sheets to help me keep track of things. I was fairly new to spreadsheets, but was able to make it do some really neat things. But it had its limitations, the main one being that I had to enter every transaction manually. It also looked terrible because I’m a back-end developer and have no sense of style, haha. I wanted to rebuild it, so I started to look at how to automate the feed in. That’s when I came across Tiller.
The foundation template is absolutely incredible, as is the Savings & Debt template. These two solutions covered about 90% of my tracker (and did most things better). There are a few things I built in my solution that I can’t live without, however. So I started to integrate them.
One of these is to essentially plot out what hard expenses will be due out of which paycheck. I created a tab for monthly expenses where you set the due day and amount. Then there’s another tab to set income information. Then finally, a third tab that takes a given check date, displays the next 6 paychecks from that date, along with a broken up view of the expenses needing paid within that period. I think it’s really neat, and I’m not sure if there’s a solution like it already in the community (didn’t look since I already had it built).
Super cool, @1Email2RuleThemAll. It sounds like Tiller is a great fit for your workflow. So happy to hear that.
Your workflow is pretty personalized and nothing in the community will match all of your feature needs precisely… plus, if it ain’t broke… but here are a few things kicking around that may inspire improvements to what you have working:
