Search code examples
arraysgoogle-sheetsimportrangegoogle-sheets-formula

In google sheet how to calcul data from another sheet

In google sheet; I have the following table

sheet_name L4 cell data
October_2021
November_2021

Each name corresponds to a sheet in the same file.

I would like to calculate the value of L4 for each sheet automatically.

Eg : first line is the value of the L4 in the October_2021 sheet.

I tested with hyperlink but it's not good, same with concatenate.

Edit 1 :

I found this solution, but if something is possible without the url I take :)

here G10 is my sheet_name column

=IMPORTRANGE("https://docs.google.com/spreadsheets/...", CONCATENATE(G10,"!L4"))
Solution

  • from one sheet to another sheet:

    ={INDIRECT(G10&"!L4"); INDIRECT(G11&"!L4")}

    from one spreadsheet to another spreadsheet:

    ={IMPORTRANGE("url_or_id"; G10&"!L4"); IMPORTRANGE("url_or_id"; G11&"!L4")}

    this is the only way