Reference IMPORTRANGE() function stored in another cell/named-range?

1
votes

To work more dynamic, I want to exchange my existing IMPORTRANGE() formulas in my sheet with my importrange-variables, where each unique IMPORTRANGE()function is stored in their own named-range, to make the code on my other sheet a little bit more flexible and clearer in case any import range changes. So I basically want to reference a formula stored in another cell/named-range. I found some solutions but nothing that deals with a IMPORTRANGE() function. I'm currently having that:

# Indexmatch with importrange
=INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/UTvlsienwl30slslielswpeowIUILEN/edit", "my_first_named_range"), MATCH(K65, IMPORTRANGE("https://docs.google.com/spreadsheets/d/UTvlsielwieoeneIlswpeowIUILEN/edit", "my_second_named_range"), 0), 12)

named range (single cell) / import_ref_01

IMPORTRANGE("https://docs.google.com/spreadsheets/d/UTvlsienwl30slslielswpeowIUILEN/edit", "my_first_named_range")

named range (single cell) / import_ref_02

IMPORTRANGE("https://docs.google.com/spreadsheets/d/UTvlsielwieoeneIlswpeowIUILEN/edit", "my_second_named_range")

Expected result:

=INDEX(import_ref_01, MATCH(K65, import_ref_02, 0), 12)

EDITABLE SAMPLE SHEET WHERE DATA IS IMPORTED:

work_with_imported_data

EDITABLE SAMPLE SHEET OF THE SOURCE TABLE:

data

2
google-sheets
share a copy of your sheet with example of desired output – player0

2 Answers

1
votes

You should surround your named ranges with double quotes "" and use INDIRECT.

=INDEX(INDIRECT("import_ref_01"), MATCH(K65, INDIRECT("import_ref_02"), 0), 12)
1
votes

I think I have something that might work for you. Here's a link to a tab on your sheet: https://docs.google.com/spreadsheets/d/1g5zbkeYDTubEH6I9Vybf6-YiCmOq9lMTFoVxcB3jYog/edit#gid=941801062&range=C2

Formula that worked:

=INDEX(IMPORTRANGE(data_sample_IR1, data_sample_IR2), 
       MATCH( B2, IMPORTRANGE( data_sample_titles_IR1, data_sample_titles_IR2), 0),
       3)

where the named ranges are:

data_sample_IR1 = " ...your data sheet key... "

WITH the quote marks, and

data_sample_IR2 = A1:Q45

and the other two named ranges are the same, but for your titles range.

This required moving the "IMPORTRANGE" text out of the named range.

If you need the flexibity of sometimes using IMPORTRANGE, and sometimes not, you could perhaps handle that with an IF wrapper around it all. Let me know if this helps.