Question on google sheets YTD comparison QUERY formula

Spreadsheets Google Sheets
1

TL;DR: why is REGEXEXTRACT(ADDRESS(1, COLUMN(Q:Q)), “[A-Z]+”) (and it’s clones) used, when “Q” would be much cleaner.

I’d like to expand the functionality of the YTD sheet, to include the current months performance against budget. In general, I’ve found the worksheet pretty easier to decipher, but I’m a little mystified by the copious use of the construct "REGEXEXTRACT(ADDRESS(1, COLUMN(Q:Q)), “[A-Z]+”)”, which is used when assembling the main QUERY lookup in cell A10. It used in many, many places, but always with literal values. In this particular example, it evaluates to the text string “Q”. I thought it might be a way to some kind of range check on the column, but that doesn’t work: for instance even if the spreadsheet only goes up to column Z, if you do REGEXEXTRACT(ADDRESS(1, COLUMN(AA:AA)), “[A-Z]+”), It would still return AA.

I’d like to ditch this construct for my own customization to make the formula more readable, especially since I’ll be returning additional columns, but I want to avoid subtle unintended consequences. I’m hoping someone can shed some light.

Thanks in advance,

Arthur

2

I did further playing around, and I now realize the intent. The following is my findings, in case someone else stumbles upon this topic.

The formula in question was:

=IF(B6="Category",

QUERY(Q:AD,"SELECT "&REGEXEXTRACT(ADDRESS(1, COLUMN(Q:Q)), "[A-Z]+")&",' ', "&REGEXEXTRACT(ADDRESS(1, COLUMN(T:T)), "[A-Z]+")&","&REGEXEXTRACT(ADDRESS(1, COLUMN(U:U)), "[A-Z]+")&","&REGEXEXTRACT(ADDRESS(1, COLUMN(S:S)), "[A-Z]+")&","&REGEXEXTRACT(ADDRESS(1, COLUMN(Z:Z)), "[A-Z]+")&","&REGEXEXTRACT(ADDRESS(1, COLUMN(AA:AA)), "[A-Z]+")&

" WHERE "&REGEXEXTRACT(ADDRESS(1, COLUMN(AD:AD)), "[A-Z]+")&"=FALSE AND "&char(64+COLUMN(Q:Q))&" IS NOT NULL AND "&char(64+COLUMN(V:V))&" !='Hide' ORDER BY "&O8&O19),

IF(B7="Category","Reset Sort By in B6",

QUERY(AF:AL,"SELECT "&REGEXEXTRACT(ADDRESS(1, COLUMN(AF:AF)), "[A-Z]+")&"

,' ',"&REGEXEXTRACT(ADDRESS(1, COLUMN(AG:AG)), "[A-Z]+")&",'  ',"&REGEXEXTRACT(ADDRESS(1, COLUMN(AJ:AJ)), "[A-Z]+")&","&REGEXEXTRACT(ADDRESS(1, COLUMN(AH:AH)), "[A-Z]+")&","&REGEXEXTRACT(ADDRESS(1, COLUMN(AK:AK)), "[A-Z]+")&

" WHERE "&REGEXEXTRACT(ADDRESS(1, COLUMN(AL:AL)), "[A-Z]+")&"=FALSE AND "&REGEXEXTRACT(ADDRESS(1, COLUMN(AF:AF)), "[A-Z]+")&" IS NOT NULL ORDER BY "&O10&O19&" LABEL '  ' ''")))

(Note that I added line breaks to improve readability) and the intent of the “REGEXTRACT(ADDDRESS…” construct was to make the Query formula (which has to be in text) robust to subsequent column insertions and deletions. But there are less verbose ways to do this, as discussed in the article Take advantage of Custom Arrays in Google Sheets and its follow-on comments.

Final thought/lesson learned: The YTD template is quite old; I suppose this is the disadvantage of using older templates as starting point: they were often built before people got more experienced with Sheets and/or Sheets functionality was augmented.