Concatenation within IMPORTRANGE

Question

I'm completely stuck on this one -- I need to use IMPORTRANGE function but want to make the sheet it pulls from flexible via concatenation -- here is my formula with a scrubbed URL:

=importrange("https://docs.google.com...",("""'"&B5&"'!$F15"&""""))

This gives me an error "Cannot find range or sheet for imported range"

When I hover over

("""'"&B5&"'!$F15"&"""")

I receive the correct string:

"'Q1 19.08.19'!$F15"

Moreover, when I try a hard-coded version of the formula:

=importrange("https://docs.google.com/...","'Q1 19.08.19'!$F15")

It provides the correct answer, which makes me think this is a structural issue, rather than my code.

Thanks so much for reading!!

J

Answer 1

On IMPORTRANGE the apostrophes aren't required. Instead of

("""'"&B5&"'!$F15"&"""")

use

B5&"!$F15"