I have a Google spreadsheet that I use to compile weekly reports. I paste the contents of each report into a new sheet in the spreadsheet. The spreadsheet has an "Overview" sheet that uses formulas to pull counts and averages from other sheets in the spreadsheet.
Sometimes columns are added or removed from the source report, which means that the field shown in column "AO" in one sheet may be in column "AM" in a different sheet. Because of this, I'm trying to use the column names (e.g., "Channel", "Time to first reply (seconds)", etc...) as a way to find the data so it can be displayed on the "Overview" sheet.
For example, one formula from the "Overview" sheet is this:
=IFERROR(averageif(M1W5!$AO:$AO,"desktop",M1W5!$AJ:$AJ)/60)
It looks at sheet "M1W5", then looks at the AO column to find all rows with the value "desktop", then it looks at that same row in the AJ column, and provides an average of all occurrences (meaning it averages up all values in the AJ column where AO = "desktop").
The problem I'm running into is this: Because the columns frequently change between sheets (column AO on sheet "M1W5" may be column AM on sheet "M2W1", etc...), I need a way to find the columns by name (row 1 of every column in every sheet contains the column name) rather than by column letter.
Is there a way to do that?
So far I've been trying to use Address and Match functions to look up the column by column name.
For example, I can use this formula on the "Overview" sheet and it will give me the exact column from sheet "M1W5" that is named "Time to first reply (seconds)":
=SUBSTITUTE(ADDRESS(1,MATCH("Time to first reply (seconds)",M1W5!$A$1:$BZ$1,0),4),1,"")
I can't figure out how (or if it's even possible) to use that inside another formula though. For example, replacing the column letters
M1W5!$AO:$AO
with the address function like so:
M1W5!SUBSTITUTE(ADDRESS(1,MATCH("Time to first reply (seconds)",M1W5!$A$1:$BZ$1,0),4),1,""):SUBSTITUTE(ADDRESS(1,MATCH("Time to first reply (seconds)",M1W5!$A$1:$BZ$1,0),4),1,"")
Ok, I figured it out.
So, my example was to have a way to reference cells in other sheets by their column header instead of the column letter.
The example I used was:
M1W5!SUBSTITUTE(ADDRESS(1,MATCH("Time to first reply (seconds)",M1W5!$A$1:$BZ$1,0),4),1,""):SUBSTITUTE(ADDRESS(1,MATCH("Time to first reply (seconds)",M1W5!$A$1:$BZ$1,0),4),1,"")
The way to actually accomplish this is by using the indirect() function, then treating the sheet!range reference like a string (i.e., put quotes around the sheet name, the colon, etc...), and use the substitute() function that returns the column letter as a string, like so (be sure to use the & to properly concat the various string values):
indirect("M3W1!"&SUBSTITUTE(ADDRESS(1,MATCH("Time to last close (seconds)",M3W1!$A$1:$BZ$1,0),4),1,"")&"2"&":"&SUBSTITUTE(ADDRESS(1,MATCH("Time to first reply (seconds)",M3W1!$A$1:$BZ$1,0),4),1,""))