Search code examples
google-sheetsgoogle-sheets-formulaimportrange

How to use the function CONCATENATE with IMPORTRANGE?

I have two spreadsheets documents:

  • Libro1, with a sheet name "Dia1"
  • Libro2, with a sheet name "Consolidado"

I use without problem the function IMPORTRANGE in the "Libro2" for get the value of cell A1 in the sheet "Dia1" of "Libro2".

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

Rta: 150

Now, I want to make the function IMPORTRANGE as a concatenation with the aim to make it parameterizable in the same sheet.

F1="Dia1" //In the cell F1 is the value for concatenate;

I tried:

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

and although the concatenation is right the result, =IMPORTRANGE("1lIpak48SxPpi4aWo20_BuquZ12zCQXekwSCEyBcYotQ";"Dia1!A1")
is a string.

Result as a string

Question:

How to set the string concatenate for the desired behavior with the result (150)?

Original question in: SOes.

Solution

  • Try:

    =IMPORTRANGE("1lIpak48SxPpi4aWo20_BuquZ12zCQXekwSCEyBcYotQ";"'"& F1 &"'!A1")