The two lists on
Accounts do not need to be in the same order. I believe the issue with the Hidden accounts mismatch is due to your extra columns in the
Accounts sheet, Acct. Display Name and Min. Bal.
Credit Card Settings in cell
AA16, the column for Hide should be column 17 (Q). With your added columns, it would be 19 (S). I’m betting yours instead displays 6 (F). To fix this, you would need to change the formula in cell
=MATCH(Z9,INDIRECT("'"&Z$7&"'!$F1:$1"),0) + 5
=MATCH(Z9,INDIRECT("'"&Z$7&"'!$H1:$1"),0) + 7
Then, use the fill handle to drag the new formula down to
AA17 to update the rest of the formulas. Everything else should stay the same, but the number and column for Hide should update.
Root Cause Analysis:
The standard way to reference another sheet in Tiller is to do so dynamically using VLookups.
This formula essentially looks at the first row, starting at the first column, and looks for a match to the column we want. It then returns the column index when the first match has been found. For this to work, the column names must be unique. Otherwise, the Vlookup will not be able to reach the second instance.
Unfortunately, the column names on the
Accounts Foundations sheet are not unique. There are two ‘Hide’ columns: one for the left side list, and one for the right. The right side is the one that is usually referenced in other sheets (such as this one), and the left side should be ignored.
To account for this issue, sheets that reference the right list of the
Accounts sheet must modify the standard formula to instead start on the column right after the left side (By default,
F). This offsets the ‘true’ index of the column, so we then also add the number of columns to the number returned to get back to that true index value.
=MATCH('Hide',INDIRECT('Accounts'!$F1:$1"),0) + 5
Your column names are unique. However, adding them where you did pushed the non-unique Hide column that belongs to the left list into the “space” typically reserved for the right list. Since the dynamic lookup on my sheet is starting at column
F, it is grabbing that first left list instance of Hide.
The formula provided for the solution will shift the lookup over by two columns to start at the first column of the right side list,