CODEWITHSUNDEEP
google-sheetssubquery
pgwozdz
pgwozdz

Reputation: 1

How to use absolute references in google sheets query formula or query by column names for multiple sheets?

I have the following problem. I'm trying to write query formula in order to put all values in one sheet by typing yes in one column, however, I have the following problem. Whenever I add a new column, references in query formula (multiple sheets) are changing and the formula doesn't work. How can I prevent this?

Or is there any way to query by a column name in multiple sheets?

I have tried locking relative references by putting sign $ I have tried to use an indirect formula to take references (data set in Helper TAB, cell E2) - nothing worked

I'm out of the ideas, for now, anyone knows how to fix this?

=QUERY({'Sheet 1'!$A:$Z;'Sheet 2'!$A:$Z;'Sheet 3'!$A:$Z;'Sheet 4'!$A:$Z}, "select * where Col1 ='yes'",0)

Here is the file I did, you can see query formula in Master sheet:

https://docs.google.com/spreadsheets/d/1XD-CECy5W5-HM5EkBFJQKDtLlykQq8Cj8ZOvDUXkd1s/edit?usp=sharing

Upvotes: 0

Views: 1988

Answers (1)

wesleymou
wesleymou

Reputation: 1

A solution to the problem is to use a formula that searches for the column address based on a reference.

=SUBSTITUTE(ADDRESS(1;COLUMN(A1);4);"1";"")

This formula will return the value A, which is the column of address A1. It can be used in query functions like this:

=QUERY(A:D;"SELECT "&SUBSTITUTE(ADDRESS(1;COLUMN(A1);4);"1";"")&"")

This function is equivalent to the function:

=QUERY(A:D;"SELECT A")

Upvotes: 0

Related Questions