Index/Match using a range from another sheet?

[Nordicrx8]

Hi All!

My google searches are coming up empty, so I'm not sure if this is even possible.

I'm trying to do a simple Index/match to grab data from another sheet, but with an added twist of instead of hard typing in the range it's pulling data from, it gets the range from another cell on a separate tab.

This is what the normal code looks like:

=INDEX('MTD Shrink'!J7:J29,MATCH(Main!AG7,'MTD Shrink'!A7:A29,0))

Here is what I want it to do:

=INDEX('MTD Shrink'!('Data Location'!O13),MATCH(Main!AG7,'MTD Shrink'!A7:A29,0))

So instead of typing in the range J7:29, it's looking in the data location tab, in Cell O13, which has J7:J29 in it.

Is this possible to do, either using index match or another formula?

Thanks so much all!

 

[Fluff]

You can do it using the INDIRECT function, it's slow, single threaded & volatile, so best avoided if possible.

 

[JEC]

Yep, try not to use Indirect. Use a macro for searching through sheets

 

[Nordicrx8]

Hmm - I have about 40 cells that I would need to do this for. do you think indirect would break on such a small number of cells?

regarding a macro, I'm not seeing how this would solve for the issue - could you provide an example?

thanks for the quick responses!

 

[Fluff]

I have about 40 cells that I would need to do this for. do you think indirect would break on such a small number of cells?

Best way to find out is to try it.

=INDEX(INDIRECT("'MTD Shrink'!"&'Data Location'!O13),MATCH(Main!AG7,'MTD Shrink'!A7:A29,0))

 

[Nordicrx8]

Best way to find out is to try it.

=INDEX(INDIRECT("'MTD Shrink'!"&'Data Location'!O13),MATCH(Main!AG7,'MTD Shrink'!A7:A29,0))

That worked perfectly! I ended up having to use two indirects, so it'll be about 80 in total. I'll start working on getting them updated now, and pray to the excel gods that it doesn't break. LOL.

Thank you!!

=INDEX(INDIRECT("'MTD Shrink'!"&'Data Location'!N23),MATCH(Main!AG7,INDIRECT("'MTD Shrink'!"&'Data Location'!N18),0))

 

[Fluff]

You're welcome & thanks for the feedback.