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]"),""))