1

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

Improve this question
0

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) &"")
Improve this answer
7
  • Thanks for the response. I updated the Test1 and Test2 spreadsheets to try to be more clear in what I am trying to do. In Test2, column A is a list of worksheets in Test1. In column B I want to get the value of cell A2 from the Test1 worksheet listed in column A. I hope that helps.
    – LarryA
    Aug 27 '19 at 18:39
  • "... " But... but... but... You did not even try the formula I gave you. I see you have exactly the same mistakes. e.g. instead of "Sheet2!A1:A2" ( or following your update "WorkSheet2!A2") you still use INDIRECT($A1&""!$A$2""). I believe no further explanations will work. Just try to understand the formula and test it. Then use similar ones for the rest of your WorkSheets. They will work for you. :)
    – marikamitsos
    Aug 27 '19 at 19:26
  • I did try that and that did work - see cell B4 in the Test 2 spreadsheet. But I don't want to hardcode Worksheet2 in the formula. I want to pick that up (WorkSheet2) from Column A.
    – LarryA
    Aug 27 '19 at 19:37
  • "...I don't want to hardcode Worksheet2 in the formula." You do not hardcode it. This is just the way it is done. You mistakenly think that using #gid=194909829 helps the formula. It does not. It is the spreadsheet ID/edit and the name of the sheet that make it work resulting to value2.
    – marikamitsos
    Aug 28 '19 at 3:46
  • Please check the edited answer. Also, as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well -. (Why vote?)
    – marikamitsos
    Aug 28 '19 at 8:32
0

  • all you need is this:

    =IMPORTRANGE("1HLuD8Y4ApxXc2Zt3osHkTTsdWBjpiOIXFSFOdslca_Q", A20)

    0

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.