yogi_Look For Teams In 'Season 1 Challenger' And Pull Name Of Opponents

Google Spreadsheet   Post  #2419

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-10-2018

question by: Dravaco
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/o8pC6uNKltU;context-place=mydiscussions

INDIRECT to a range reference in a different sheet doesn't work.

So what I'm trying to do is create a (dynamic) cell range referencing a range in another sheet. This is what I've been trying:

=MATCH($E$2, INDIRECT("'Season 1 Challenger'!B4:'Season 1 Challenger'!B19"), 0)

=MATCH($E$2, INDIRECT(("Season 1 Challenger!" & ADDRESS(7, ((A4 - 1) * 8) + 2)) & ":" & ("Season 1 Challenger!" & ADDRESS(8, ((A4 - 1) * 8) + 2))), 0)

=MATCH($E$2, INDIRECT(ADDRESS(4, ((A4 - 1) * 8) + 2, 1, True, "Season 1 Challenger") & ":" & ADDRESS(19, ((A4 - 1) * 8) + 2, 1, True, "Season 1 Challenger")), 0)

Every single one of these returns the error:

Function INDIRECT parameter 1 value is "Season 1 Challenger'!$B$4:'Season 1 Challenger'!$B$19'. It is not a valid cell/range reference.

When I try the same things, but just making a cell reference instead of a range reference, it works perfectly fine, but I need a range reference.

If it helps, I can share the sheet.