Saturday, August 25, 2012

How to use importRange with dynamic sheet name

How to use importRange with dynamic sheet name

Question:

How to use importRange with dynamic sheet name

Answer:

I have two spreadsheets, let's call them "SpreadsheetA" and " SpreadsheetB". 

"SpreadsheetA" has several sheets inserted. Now from within "SpreadsheetB" I would like to get the data from cell B4 of "SpreadsheetA", but depending on the sheet-name I insert in a cell (A1 in this example) within "SpreadsheetB".

I have inserted the following formula in Cell B3 in "SpreadsheetB" to get the desired results:
=ImportRange("0AmMTqpzD9YRndFRjTDBBc3VxTlFub0pTLUVKWkVIdVE";CONCATENATE(A1;"!B4"))


Formula "ImportRange" has two arguements first one is key and second is a range.
Now here we have to give the key of "SpreadsheetA", you can get the key of the any spreadsheet from the URL.

Look at following screenshot of "SpreadsheetA" (with "Sheet1" as active sheet):


In above screenshot, I have marked the key of "SpreadsheetA" with red color.

So in first argument of formula "ImportRange" we have to put key value: "0AmMTqpzD9YRndFRjTDBBc3VxTlFub0pTLUVKWkVIdVE"

And second argument is: CONCATENATE(A1;"!B4"), which will give us whatever contents of "Cell A1" (that is "Sheet1") + "!B4"


Look at the following screenshot of "SpreadsheetB":



In above screenshot of "SpreadsheetB", we are having "Sheet1" as value in "Cell A1", 
So formula CONCATENATE(A1;"!B4") will give us "Sheet1!B4".

And finally formula ImportRange will give us result: "Pink"

Look at the screenshot of "SpreadsheetB" above, Cell B3 in SpreadsheetB contains following formula:
=ImportRange("0AmMTqpzD9YRndFRjTDBBc3VxTlFub0pTLUVKWkVIdVE";CONCATENATE(A1;"!B4"))

I hope this will make you understand the formula ImportRange.



I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,

4 comments:

  1. Can you do the same thing with the key itself?

    Say I have created a Form Template (A) that feeds a Data spreadsheet (C) and a Template(B)with multiple pages for different time periods that uses the data sent by the form to (C) . Now I want to use several versions of the form Template for Different groups (a, b, c) but use the same Template that uses that data for each user. Could I have the importrange function in (B) ,the Data Template, reference a cell on a background data sheet so that I would only have to update the key in one cell of (B) and all the sheets would have the correct Key?

    example: On Spreadsheet (B)-
    =query( importrange("BackgroundData!B2"; "Form Responses 1!B2:I"); "select * where Col1 = Yellow "; 0 )

    Where:
    BackgroundData!B2 = A cell on a sheet in spreadsheet (B) that contains the key for spreadsheet (C)

    ReplyDelete
    Replies
    1. Posted the same question here:

      http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/how-do-i/desktop/NLw6T5Gx_b8

      Delete
  2. Even better would be to use Apps Scripts to automatically fill A1 with the latest Sheet name

    ReplyDelete
  3. Is it possible to automatically fill A1 with each corresponding "SpreadsheetB" sheet name? Thanks

    ReplyDelete