1

I have a dynamic table where columns are moved every now and then. Therefore, I would like to reference my column name in my query. Unfortunately I do not know so well and the internet raises more questions.

My formula looks like this:

=query('X Source'!A:AP, "select D, E, AA, AM, X, A where "&if(month(now())=1,"(month(A)<11)","(month(A) <=month(now())-2)")&" and (V like 'C & G' or V like 'SAS' or V like 'SXS D' or V like 'DIR') Order By A desc")

Where:

D = Cinter E = Cluster AA = Creation Date AM = Change Ow X = Title A = Date

Do you have any ideas? I would like not to write a script.

I have already tried with the function filter to bypass but there I get no further because of the filtering after month.

={FILTER('X Source'!AA:AA, 'X Source'!V:V="SAS",'X Source'!X:X<>"%BY SB%",'X Source'!X:X<>"%SB ONLY%", month('X Source'! AA:AA)=month(today())-1);FILTER('X Source'!AA:AA,'X Source'!V:V="SXS D",'X Source'!X:X<>"%BY SB%",'X Source'!X:X<>"%SB ONLY%"
Improve this question

3 Answers 3

Reset to default
1

You could use MATCH to find the numbers of columns, and grab your range in curly brackes so you can refer them as Col1,Col2,Col3 instead of A,B,C

Just to make it more dinamic and you could change your range, I wrapped it in LAMBDA. With the headers I matched all your values and joined them with comma. 'date' only matched that colum. Column V I had my doubts about if it was a mistake when you said which columns was located at which headers. Please change "Title" in ""Col"&MATCH("Title",INDEX(range,1),0)" to the actual desired header title (that now is in V column) so it matches correctly:

=LAMBDA(range, LAMBDA(headers,date,title,query({range}, "select "&headers&" where "&if(month(now())=1,"(month("&date&")<11)","(month("&date&") <=month(now())-2)")&" and ("&title&" like 'C & G' or "&title&" like 'SAS' or "&title&" like 'SXS D' or "&title&" like 'DIR') Order By "&date&" desc"))( JOIN(",",INDEX("Col"&MATCH({"Cinter","Cluster","Creation Date","Change Ow","Title","Date"},INDEX(range,1),0))), "Col"&MATCH("Date",INDEX(range,1),0), "Col"&MATCH("Title",INDEX(range,1),0) )) ('X Source'!A:AP)

In my dummy example with random columns, the inside part of the query would look like this:

"select Col7,Col3,Col15,Col20,Col11,Col12 where (month(Col12)<11) and (Col11 like 'C & G' or Col11 like 'SAS' or Col11 like 'SXS D' or Col11 like 'DIR') Order By Col12 desc"

UPDATE with other Locale

=LAMBDA(range; LAMBDA(headers;date;pl;query({range}; "select "&headers&" where "&if(month(now())=1;"(month("&date&")<11)";"(month("&date&") <=month(now())-2)")&" and ("&pl&" like 'C & G' or "&pl&" like 'SAS' or "&pl&" like 'SXS D' or "&pl&" like 'DIR') Order By "&date&" desc";1))( JOIN(",";INDEX("Col"&MATCH({"Cinter";"Cluster ";"Creation Date";"Change Owner";"Title";"Date"};INDEX(range;1);0))); "Col"&MATCH("Date";INDEX(range;1);0); "Col"&MATCH("PL";INDEX(range;1);0) )) ('X Source'!A1:AS)

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

docs.google.com/spreadsheets/d/… here is my dataset I really dont know. In sheet 3 there should pop up the filtered data.
It was an issue with your locale, had to change all commas into ";", and Cluster title actually had an extra space. Updated answer, check with image in your example!
Thank you so much! I have just one last question. I need to change the formula a bit for different thinks. I tried it out but I could not figure it out. I put it in the dummy document. Maybe u can help me a last time. Everytime I have to change a bit from the formula I get stuck.
Apparently some time you were editing the first formula you had some quotation mark misplaced, and it added some extra parenthesis at the end. Also, if you're adding or deleting variables in LAMBDA (like "title" in the formula in column H) you have to add or remove its definitions; meaning, in this case, expressions like: MATCH("Title";INDEX(range;1);0)
1

Here's my formula based on this post which is much shorter:

=QUERY(INDIRECT(A4&"!A1:Z"),"SELECT "&SUBSTITUTE(ADDRESS(1, MATCH(A5,INDIRECT(A4&"!A1:Z1"),0), 4),1,""))
  • Put sheet name into A1
  • Put header name (first row) into A2
  • It will also include the header name in your query, if you want to remove it modify the first INDIRECT function and set it to INDIRECT(A4&"!A2:Z")

You can also add this function into a named function like this for ease use in other formulas: named function version for google sheets

Improve this answer

1 Comment

This only seems to work if data starts with Column A...not a universal solution.
1

You can define the following Named Function. (Data > Named functions > Add new function)

Name:

BETTERQUERY(range, better_query, headers)

Definition

=QUERY({range},IF(IFERROR(SPLIT(better_query,"`")=better_query,1),better_query, REGEXREPLACE(REDUCE(better_query,REGEXEXTRACT(better_query,REGEXREPLACE( REGEXREPLACE(better_query,"([()\[\]{}|\\^$.+*?])","\\$1"),"`(.*?)`","`($1)`")), LAMBDA(acc,cur,SUBSTITUTE(acc,cur,IFNA("Col"&MATCH(cur,INDEX(range,1),0),cur)))), "`(Col\d+)`","$1")),headers)

And then use it like this:

=BETTERQUERY('X Source'!A:AP',"select `Cinter`, `Cluster`, `Creation date` ...",1)

For more information on how this works see How to Use Column Names in QUERY

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.