Is it possible to have multiple return ranges located in multiple forms with INDEX COLLECT?

Jordan1990

For example, pulling from one other sheet will use the following:

=index(collect(return RANGE, criteria RANGE 1, criteria 1, ,1)

I am looking for the INDEX COLLECT function to return values from multiple sheets depending on if the criteria range matches my @row. This will be the only criteria I search on.

Thanks!

Jeff Reisman

@Jordan1990

It's possible to use several INDEX/COLLECTS inside of nested IF statements. For example:

=IF(Name@row = "Jeff", INDEX(COLLECT(return RANGE, criteria RANGE 1, criteria 1, ,1), IF(Name@row = "Charlotte", INDEX(COLLECT(return RANGE2, criteria RANGE 2, criteria 2, ,1), ""))

Jeff Reisman

@Jordan1990

It's possible to use several INDEX/COLLECTS inside of nested IF statements. For example:

=IF(Name@row = "Jeff", INDEX(COLLECT(return RANGE, criteria RANGE 1, criteria 1, ,1), IF(Name@row = "Charlotte", INDEX(COLLECT(return RANGE2, criteria RANGE 2, criteria 2, ,1), ""))

Jordan1990

Thank you, this makes sense however I am still receiving an error message. Instead of clicking the reference another sheet button for each INDEX statement, I merely changed the Sheet name in the formula text string. Think that could be my issue?

Jordan1990

Yup looks like I had to enter in each sheet link using the "reference another sheet" link for each one. No worries, this is all fixed now. Thanks!!!!