Tuesday, April 10, 2018

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

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.