Interactive reports using QUERY and IMPORTRANGE

Using the formula =IMPORTRANGE in a Google sheet allows you to easily bring through data from other sheets. For some more powerful functions you can combine QUERY and IMPORTRANGE as in the examples below, and make these interactive using dropdowns or values directly entered in a cell

On one sheet I have uploaded some historical data on NIHR Trust performance, published on the Guardian's Clinical Research Zone.  I will then use this spreadsheet as the source to bring in selected data on another target sheet.

In the first example, using data validation, I have put in a dropdown in cell B3 containing all the different trust types.  Using the following formula, I can then return the list of trusts that match exactly the value selected in that cell:

=(QUERY(IMPORTRANGE("15Q9ppH0-Pcdwak0Eyns4NNtMMv0Ft0PeqGf2RDfWot4","A:k"),"SELECT Col1, Col2, Col4 WHERE Col3 =" & "'" & B3 & "'"))

In the next example, I have amended the dropdown to include some shorter keywords, that will match more items.  I have then adjusted the formula, so that it does not look for an 'equals' match, but instead a 'contains'

=(QUERY(IMPORTRANGE("15Q9ppH0-Pcdwak0Eyns4NNtMMv0Ft0PeqGf2RDfWot4","A:k"),"SELECT Col1, Col2, Col4 WHERE Col3 CONTAINS" & "'" & B3 & "'"))

In the next example, I wanted to show all the trusts that have above a certain number of recruiting studies. Instead of a dropdown, I just have a cell that users can type a number in.   The formula for this one is:

=(QUERY(IMPORTRANGE("15Q9ppH0-Pcdwak0Eyns4NNtMMv0Ft0PeqGf2RDfWot4","A:k"),"SELECT Col1, Col2, Col7 WHERE Col7 >"&B3))

In the final example, I wanted to use a SUM function, to calculate the total number of studies for each of the trust types, and apply a custom title/label "Total number of studies" to that column.  The formula for this is

=(QUERY(IMPORTRANGE("15Q9ppH0-Pcdwak0Eyns4NNtMMv0Ft0PeqGf2RDfWot4","A:k"),"SELECT Col3, sum(Col7) where Col3 contains"& "'" & B3 & "'" &"Group by Col3 label sum(Col7) 'Total number of studies' "))

NB - a quirk of the new Google sheets, is that you need to authorise a connection between the source and the target sheet when you do an IMPORTRANGE.   When you use a standard IMPORTRANGE, it gives an error and prompts you to do this as in the screenshot below:

Unless you have already made this connection, using IMPORTRANGE the QUERY(IMPORTRANGE(  function will not work.  To enable this to work for my demo sheet, all I needed to do was a simple IMPORTRANGE formula on another sheet such as:

=importrange("15Q9ppH0-Pcdwak0Eyns4NNtMMv0Ft0PeqGf2RDfWot4","B:B")

Once the connection has been authorised, the other formulae will work as expected.

  

A copy of the sheet with the formulae in can be downloaded here: