Is it possible to link a cell inside of the IMPORTRANGE formula in Google Sheets?

Question

Scenario:

I have a master Google Sheets spreadsheet with dozens of pages linking to Data Studio.

Each page has an IMPORTRANGE formula linking to other Google Sheets spreadsheets.

These other spreadsheets have multiple pages and are created monthly, meaning a new URL for each spreadsheet.

I want to:

Have one master spreadsheet to avoid re-linking to Data Studio for each new spreadsheet.

I want to set up a page in the master spreadsheet where I can drop in the new URLs of the other spreadsheets, which will then update the URLs inside of the IMPORTRANGE formulas across the document.

My Attempts

Using IMPORTRANGE, my syntax looks like:

=IMPORTRANGE("(IMPORTRANGE(“*MASTER SPREADSHEET URL*”, “*LINKS PAGE*! B1”))","*OTHER SPREADSHEET PAGE*!A1:Z500")

This brings back the error "Spreadsheet cannot be found", although it does bring back a link if I only input

=IMPORTRANGE(“*MASTER SPREADSHEET URL*”, “*LINKS PAGE*! B1”)

Even linking to a URL on the same page doesn't work, and brings back the error "Spreadsheet cannot be found"

=IMPORTRANGE("('B5')","Display Overview!D1:Z500")

What syntax should I use here, or can you recommend a better approach to this issue?

I really appreciate your feedback.

Answer 1

=IMPORTRANGE("(IMPORTRANGE(“*MASTER SPREADSHEET URL*”, “*LINKS PAGE*! B1”))","*OTHER SPREADSHEET PAGE*!A1:Z500")

That formula has several issues, including:

=IMPORTRANGE([1: "(] [2:IMPORTRANGE(“\*MASTER SPREADSHEET URL\*”,] “*LINKS PAGE*! B1”)[1: )"],"OTHER SPREADSHEET PAGE!A1:Z500")

• [1] no quotes or parentheses required here
• [2] if the 'LINKS PAGE' sheet is in the same spreadsheet file where this formula resides, you should not use importrange() to read the value; use a simple range reference instead

To import from the spreadsheet identified in cell B1 on the 'LINKS PAGE' sheet in your master spreadsheet, use this:

=importrange('LINKS PAGE'!B1, "OTHER SPREADSHEET PAGE!A1:Z500")