Google-sheets – I am having trouble using indirect to access a cell in a different Google Spreadsheet

formulasgoogle sheetsimportrange

I have 2 spreadsheets, Test1 and Test2. In Test2, Cell B2 I want to access a cell in test1. I want to use the value in cell A1 (the value is test1) indirectly to access cell A2 in test1. This is the formula I use and am getting a Formula Parse Error. I have also included links to the test spreadsheets:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1HLuD8Y4ApxXc2Zt3osHkTTsdWBjpiOIXFSFOdslca_Q/edit#gid=194909829","INDIRECT($A1&""!$A$2"")")

Test1: https://docs.google.com/spreadsheets/d/1HLuD8Y4ApxXc2Zt3osHkTTsdWBjpiOIXFSFOdslca_Q/edit#gid=194909829

Test2:
https://docs.google.com/spreadsheets/d/10TA2hEwiDc9g-9_V30TQImZsgJVzVA2WFZifmbdL0d4/edit#gid=0

Best Answer

If what you want is to import to Test2 from Test1 a range of cells (A1:A2), the formula should be:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1HLuD8Y4ApxXc2Zt3osHkTTsdWBjpiOIXFSFOdslca_Q/edit", "Sheet2!A1:A2")

If on the other want to import just a single cell (A1) the formula would be:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1HLuD8Y4ApxXc2Zt3osHkTTsdWBjpiOIXFSFOdslca_Q/edit", "Sheet2!A"&"1")

Please pay attention on the second part of the formula:

  • for a range of cells: "Sheet2!A1:A2"
  • for a single cell: just "Sheet2!A1" OR "Sheet2!A"&"1"

There is no need to use INDIRECT
(I must admit I am a bit confused about what you want)

EDIT

(following your clarifications in the comments)

Instead of INDIRECT you should use INDEX.
This way your variables and formulas in e.g. cell A9 of spreadsheet Test2 would be:

  • for a range of cells: WorkSheet2!A3:B7
  • for a single cell: just WorkSheet2!A1

In cell B9 of spreadsheet Test2 use the formula: 

=IMPORTRANGE("1HLuD8Y4ApxXc2Zt3osHkTTsdWBjpiOIXFSFOdslca_Q/edit",""& INDEX(A9,,1) &"")
Related Topic