IMPORTRANGE built dynamically, pulls in as string when it lands in cell instead of the formula

Question

enter image description here

I'm building linked google sheets. In the scenario I need to build the IMPORTRANGE formula dynamically from a concatenation.

The IMPORTRANGE formula which I want to populate in the cell is: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1MULmCled46FWynp7fmGGAQBflUn6lpgltl9XNqXvnJ8/edit#gid=328066760","Competitive Analysis!E9")

The URL in the formula will change based on some variables.

I have built the IMPORTRANGE formula in a different sheet using concatenation and am inserting into the cell with a vlookup:

=VLOOKUP(E2,'Tool Setup'!A7:F22,6,FALSE)

The VLOOKUP formula causes the IMPORTRANGE FORMULA to show up as string text, not as a formula.

Is there something I can do to force it to register as a formula in the cell?

Answer 1

I don't think you can do it quite the way you've described.

What should work is to have the root of the formula in the cell where you want the data to end up, but then complete it with data pulled from another cell (or sheet).

For example, in the cell where you have the VLOOKUP, change that to be:

=IMPORTRANGE(VLOOKUP(E2,'Tool Setup'!A7:F22,6,FALSE),VLOOKUP(.....))

where the first VLOOKUP would bring back the URL, and the second would bring back the range.

I believe this should work.