Query/import range formula doesn't work until page refresh

Question

I have a sheet where I use Query and Import Range formula as given here

=Query({Importrange("URL","'Trial'!A2:F");
Importrange("URL","'Trial'!A2:F")}, 
"Select Sum(Col6) Pivot ("&F1&")")

It works fine, but whenever I change the reference for the Pivot in Cell F1, it gives:

Error # Value : Error Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col6.

Once the sheet is refreshed, it works fine, this has to be repeated whenever the Reference cell value is changed..

Need Help to solve this so that the data is automatically spooled upon change in Pivot reference cell.

Max Makhrov Max Makhrov · Accepted Answer · 2017-12-27T09:05:25

As Kevin mentioned on a forum, the issue is refreshing importrange formula

The way to check:

use a separate sheet to make your importrange: ={Importrange("URL","'Trial'!A2:F"); Importrange("URL","'Trial'!A2:F")}
In a current sheet use reference to the results of import: =Query({import results}, "Select Sum(Col6) Pivot ("&F1&")")