This is more of a ‘Tell’ than a show. In the work converting my Transaction Comparison template from Google Sheets to Excel, I got most everything finished, and copied the sheet to a dummy workbook for sharing. I went to ‘Edit Links’, changed to the new source, and got an error. Apparently if you have formulas that reference a link (eg. Transactions[Tags]), and the new source doesn’t have a Tags column on their Transactions sheet, you can’t change the source, and the template is useless. OUCH!
I didn’t want to have to force users to create a Tags column just to use my template. After much mulling about, I had an idea while mowing the lawn. What if I hide the reference in an INDIRECT command? I quickly got to my computer, created a cell (V8) that contained the text “Transactions[Tags]”, and updated my formulas to use “INDIRECT(V8)” in place of “Transactions[Tags]”. I hit enter and the formulas still worked! The big test then was to copy the sheet to my dummy workbook. I edited the links, changed the source, and bingo, the source updated and the dummy worksheet data filled into the new template. The Tags column in the new template remains blank, as expected. I created a new Tags column in the dummy workbook Transactions sheet to see if the template would use it, and it did. Newly added tags showed up in the Tags column in my template.
I had tried Googling around for options on this, but it’s a hard topic to get relevant hits on. Not sure if others have run into this yet or not, or if there is a better solution, but this is how I got around it, seems to work well, I’ll know more once people start downloading the template!
That’s really neat! I was using iferror, but if you don’t have a Tags, then it becomes iferror(#ref) and if you then add a Tags column, it’s still iferror(#ref), so I think this is much better and I’ll have to edit mine to match and see if this works.
That’s a very creative use of INDIRECT()! I use it in combination with reference range lookups (like most of the Tiller templates), but it never occurred to me to use it as a form of error handling. Nice work @jpfieber!
I got it working in my tracker. I put in a manual cell with Transactions[Tags] and then I had a list pulling in the tags, with an iferror if it’s not there.
In the query, I was able to use the indirect as is, but for the selection of the columns, I still had to do an iferror because if there were no tags present, then it didn’t know what to pull. This is the first part of the query for reference:
Good addition. In my case, I staged the tag info in the hidden ‘helper data’ so I didn’t care if their was an error showing, but the IFERROR is still a nice addition. Thanks!
When I tried to Edit Links while importing the template into a workbook without a Tags column I was getting an error that wouldn’t let me proceed, so the template couldn’t be used in the new workbook. Using the Indirect command got around that, allowing the Edit Links to work.
Couldn’t the formula be this? =FILTER(CHOOSE({1,2,3,4,5,6},Transactions[Date],Transactions[Description],Transactions[Category],Transactions[Amount],Transactions[Account],IFERROR(INDIRECT("Transactions[Tags]"),""))