Google sheets query indirect question

[Ronderbecke]

I am trying to figure out why the formula I am using doesn't work. Here is what works:

=Query(Calculator!B2:BP,"select * order by B desc")
But I need the "B" to be variable based on a cell so I am using this function which should be exactly the same but doesn't work:
=Query(Calculator!B2:BP,"select * order by '"&indirect("A2")&"' desc")

Any help would be greatly appreciated. Been spending forever trying to figure out why it's not working.

 

[Bosquedeguate]

can you provide a few rows of your Calculator Sheet (I assume Google Sheets)?

Try this. If it does not work please send some rows from your Sheet.

=Query(Calculator!B2:BP,"select * order by '" & Range("A2") & "' desc") - the value in Range("A2") needs to be the Column (e.g. "D") you are sorting on.

 

[Ronderbecke]

It's weird cause it just says range is an unknown function? Here is what the columns look like

Customer Name	Sales Representative	Date of Sale	Price Sheet
Anthony Hunter	Caryn Bladzik	6/3/2022	5/14/2022
James Nancy O'banion	Hennessey Costigan	9/19/2022	9/6/2022
Angie Wilson	Hennessey Costigan	10/4/2021	9/17/2021
Tad Marshburn	Caryn Bladzik	7/1/2022	5/14/2022

 

[Bosquedeguate]

can you provide a few rows of your Calculator Sheet (I assume Google Sheets)?

Try this. If it does not work please send some rows from your Sheet.

=Query(Calculator!B2:BP,"select * order by “ & Range("A2") & " desc") - the value in Range("A2") needs to be the Column (e.g. "D") you are sorting on.

Here is what works

=Query(Calculator!B2:BP,"select * order by '" & A2 & "' desc")

Where cell A2 contains the column you want to sort on, e.g A, B, C …

If your query range includes header row(s) specify the number of header rows as the 3rd arg to the Query function.

 

[Ronderbecke]

=Query(Calculator!B2:BP,"select * order by '" & A2 & "' desc") isnt working either?
Here is what I put in A2:

 

[Bosquedeguate]

Your Query results cannot be in the same range as your source data. Try putting your sorted data on a different sheet or into different columns on that same sheet.
But, the Query will not work if it attempts to overwrite the data that it is Querying.

Example below with some of your data.

C	Customer Name	Sales Representative	Date of Sale	Price Sheet
	Anthony Hunter	Caryn Bladzik	6/3/2022	5/14/2022
	James Nancy O'banion	Hennessey Costigan	9/19/2022	9/6/2022
	Angie Wilson	Hennessey Costigan	10/4/2021	9/17/2021
	Tad Marshburn	Caryn Bladzik	7/1/2022	5/14/2022
Sorted Output	formula in Cell below: =Query(B1:E5,"select * order by " & A1 & " asc, D", 1)
	Customer Name	Sales Representative	Date of Sale	Price Sheet
	Anthony Hunter	Caryn Bladzik	6/3/2022	5/14/2022
	Tad Marshburn	Caryn Bladzik	7/1/2022	5/14/2022
	Angie Wilson	Hennessey Costigan	10/4/2021	9/17/2021
	James Nancy O'banion	Hennessey Costigan	9/19/2022	9/6/2022

 

[Ronderbecke]

Your Query results cannot be in the same range as your source data. Try putting your sorted data on a different sheet or into different columns on that same sheet.
But, the Query will not work if it attempts to overwrite the data that it is Querying.

Example below with some of your data.

C	Customer Name	Sales Representative	Date of Sale	Price Sheet
	Anthony Hunter	Caryn Bladzik	6/3/2022	5/14/2022
	James Nancy O'banion	Hennessey Costigan	9/19/2022	9/6/2022
	Angie Wilson	Hennessey Costigan	10/4/2021	9/17/2021
	Tad Marshburn	Caryn Bladzik	7/1/2022	5/14/2022
Sorted Output	formula in Cell below: =Query(B1:E5,"select * order by " & A1 & " asc, D", 1)
	Customer Name	Sales Representative	Date of Sale	Price Sheet
	Anthony Hunter	Caryn Bladzik	6/3/2022	5/14/2022
	Tad Marshburn	Caryn Bladzik	7/1/2022	5/14/2022
	Angie Wilson	Hennessey Costigan	10/4/2021	9/17/2021
	James Nancy O'banion	Hennessey Costigan	9/19/2022	9/6/2022

So basically I would Query over to the new sheet from the old one, then run a query on that sheet from what is already pulled over so the value is on the same sheet? That would require me to query to cell BT:EH for the first query, and then cell B:BP would be the new query with your formula? In doing it that way it still doesn't sort the data though?

 

[Bosquedeguate]

What I would do is create a sheet to do your Query (including) any sorting you want to do.

Your query will reference your source data by using the sheet name in the range, e.g. =Query(Calculator!B2:E5, ….)

As far as sorting is concerned can you provide the formula? I am not having any problems with SQL in the query formula.)