I’m using the Google Foundation Template, and want to pair the two transactions of a transfer between bank accounts. (I’m aware of “Automatic Transfer Matcher with Clickable Links”.)
I created a column named “Twin” on the Transactions sheet. For both sides of a transfer between accounts, I manually create a hyperlink in the Twin column to the opposite transaction. These hyperlinks initially work OK.
However, after I do a “Fill” (Extensions → Tiller Money Feeds → Fill Sheets), my hyperlinks are broken. For example, suppose I have a pair of transactions in rows 2 & 3 that constitute a transfer between accounts. In the Twin column, each hyperlink points to the opposite row. When “Fill” runs, it adds, say, 4 new transactions at the top of the sheet. So my pair of transactions and their hyperlinks are now in rows 6 & 7. But the cell addresses in the hyperlinks have not been updated, and still point to rows 2 & 3.
I’ve been trying to diagnose this problem. So far, I think it occurs only if the Tiller Foundation worksheet is open when Filling occurs. This leads me to wonder if the root cause is simultaneous updates to Google Sheets. ie, Tiller filling updates the copy in the cloud, and I have a copy open in my browser. My copy gets the new rows, but not any of the updates to the hyperlinks.
I find it difficult to believe that Google has a bug that effects hyperlinks in this way, but I suppose it is possible. Another possibility might be that Tiller’s Filling process somehow buggers up the hyperlink updating.
Can anyone replicate and confirm this scenario?
Any speculation regarding the root cause of these broken hyperlinks?
This is my screenshot after filling has broken the links. Filling added rows 2 thru 7. So row 8 was on row 2 when I created the hyperlinks in what is now cells I8 and I9. The link in cell I8 should point to C9, but now points to B3. The original formula for cell I8 was “Transactions!C3”.
I expected that filling would add new rows 2 thru 7, AND update the formula in cell I8 to: “Transactions!C9”. Instead it was updated to “Transactions!B3”.
I just noticed that my original links all pointed to column C, but now point to column B. This might be a clue.
I think you’d need to use a formula something like this: =HYPERLINK("#gid=####range=A"&ROW(C2),"Twin")
where the ROW C2 cell reference is outside the quotes and so it will be change relatively when rows are inserted above it. e.g. after inserting one row above it, C2 will change to C3.
Replace #### with the gid number of your Transactions sheet found in the browser address bar.
The variable cell reference could also be used in the link name, something like: =HYPERLINK("#gid=1256593101range=A"&ROW(C2),"Row"&ROW(C2)&" - "&C2)
You wouldn’t need to do a fill to test it, just manually insert a row above the link to see what happens.
Hi Mark. Thanks for the suggestion. I changed my links as you suggested (first version only). However, the updated links are still broken after filling.
My Filling test added two new transactions. But the broken links are now off by 4 rows. This is a different symptom - before the link pointed to a row that was off by the number of transactions that filling added. On the good side, Filling did not change the column (Prior to using your formula, filling was changing the linked column from C to B).
After updating with your links (but before filling), they worked correctly as expected.
Could you try implementing my scenario please? I’d like to know that this works correctly for at least one other person. ie,
On Transactions tab, add a column for links.
On two rows, create a pair of hyperlinks (using your formula) that each point to the opposite row.
Do a fill that adds at least a couple new rows. My experience is that simply manually adding new rows at the top of the sheet is not sufficient to exhibit the problem.
Check if the hyperlinks each still point to the correct rows. Yes/No?
Hey @dav.kellogg, I did a fill this morning and my hyperlinks still point to the correct rows.
The fill added 8 new transactions.
Here is what the hyperlink formulas looked like after the fill.
Twin26/Twin17 linked to rows 26/17, respectively, before the fill (before fill row number in the link name).
Both C34 and C25 represent post-fill increment by 8, the number of new transactions above the links. =HYPERLINK("#gid=####range=A"&ROW(C34),"Twin26") =HYPERLINK("#gid=####range=A"&ROW(C25),"Twin17")
The corresponding cell snapshots:
A fill essentially adds transactions to the sheet and then sorts it so the new transactions are at the top of the sheet.
I’m wondering if you might have some other data filtering turned on that is interacting with the sort
One test might be to reset your data filter by selecting the entire sheet and toggle data filter off/on.
Also remove any data filter views: Menu > Data > Filter views
@Mark.S I think I’ve solved my filling problem. The problem (I believe) is that I had a filter, though nothing was filtered out. I don’t understand how/why this would break hyperlinks, but accept it as the cause.
As a countermeasure, I suspect that there is a way using scripts to remove an active filter when I close the file. Could you point me to some magic functions that would accomplish this? Something like:
OnClose()
{
Remove DataFilter();
}
Thank you for your generous help in solving the filling/hyperlink problem - I REALLY appreciate it.
Good to hear resetting the data filter solved the issue.
I tried recording a macro to toggle the data filter off/on, and then set an On open Trigger.
I tested it out by setting a data filter on my Transactions sheet, selected a different sheet, closed the Tiller spreadsheet browser tab, and upon re-opening the spreadsheet in a new browser tab the macro ran and reset the Transactions sheet data filter.
This macro also has a Ctrl+Alt+Shift+1 hotkey assignment that will execute it on-demand.
Here’s the code.
Should be able to access Macros and Apps Script via menu > Extensions.
macros.gs
function resetDataFilter() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Transactions'), true);
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
spreadsheet.getActiveSheet().getFilter().remove();
sheet = spreadsheet.getActiveSheet();
sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).createFilter();
spreadsheet.getRange('A2').activate();
};
