Home / Questions / 31765
Accepted
Goerman
Asked: 2020-11-05 05:58:22 +0800 CST 2020-11-05 05:58:22 +0800 CST

How to use CONCATENATE function with IMPORTRANGE in google spreadsheet?

  • 772

Scenery:

I have two spreadsheet documents from google drive:

  • Book1 , with a sheet called "Day1"
  • Book2 , with a sheet called "Consolidated"

I use the IMPORTRANGE function in Book2 to fetch the value from cell A1 of sheet Day1 from Book1 without any problem.

=IMPORTRANGE("1lIpak48SxPpi4aWo20_BuquZ12zCQXekwSCEyBcYotQ";"Dia1!A1")

Rate: 150

I now want to build the IMPORTRANGE function as a concatenation in order to make it parameterizable within the same sheet.

F1="Dia1" //In cell F1 the value to be concatenated is located;

Try:

=CONCATENAR("=IMPORTRANGE(""1lIpak48SxPpi4aWo20_BuquZ12zCQXekwSCEyBcYotQ"";""";F1;"!A1"")")

Although the concatenation is correct, it returns the value =IMPORTRANGE("1lIpak48SxPpi4aWo20_BuquZ12zCQXekwSCEyBcYotQ";"Dia1!A1")as a string.

enter image description here

Ask:

How to indicate that I execute the concatenated string so that it gives me the desired result (150)?

google-hoja-de-cálculo

1 Answers

  1. Best Answer

    Formula

    =IMPORTRANGE("1lIpak48SxPpi4aWo20_BuquZ12zCQXekwSCEyBcYotQ";CONCATENATE(F1;"!A1"))

    Explanation

    1. Adding a formula
      To add a formula, you type the equal symbol =at the beginning of the formula. This is only done once and only once per cell.

    2. Nesting functions
      Placing a function as an argument to another function is called nesting functions.

    3. arguments

    In this case, what you want is to concatenate a cell value with some text to get the text string argument of the reference, so CONCATENATE should be used nested within IMPORTRANGE and not the other way around.

    • 1