Having duplicate column names in the Accounts sheet (“Account” and “Group”) complicates things when I’m trying to come up with a common way to determine the Column letters for the Accounts sheet. I’m looking for some best practices.
My current workaround is to change the columns we’re looking for when it’s the Accounts sheet…
=if(or(isblank(K12),isblank(L12)),iferror(1/0),REGEXEXTRACT(ADDRESS(1,MATCH(L12, if(K12=“Accounts”,indirect("’"&K12 &"’!$F$1:$1"),indirect("’"&K12 &"’!$A$1:$1")), 0)+if(K12=“Accounts”,code(“F”)-code(“A”),0)), “[A-Z]+”))
where K = the sheet name and L = the column name you’re looking for in the sheet.
The issue is that this assumes the columns with unique names I want to search for in the Accounts sheet always start in column F. If I insert any column to the left of F, this formula breaks down.
One solution would be to change the Accounts sheet Column headers so there are not duplicate header names (either A and C, and where the first Account and Group names are in my sheet; or H and P where they are duplicated). If I change H and P names, I am concerned about lack of compatible with any Community solution you download. If I change A and C, will the Tiller feed get confused on where to put new accounts?
Another option is that I think there should be a way to dynamically determine the column offset for the Accounts tab for the unique names (currently F in my Accounts sheet). This creates a circular dependency in my formula above.
I was also thinking that, if your looking for the Account and Group columns in the Accounts sheet, you pick the 2nd instance, not the first.
Looking for your best practices.
Thanks a lot,