INDIRECT Formula

[James02]

Hi, I am trying to tie two INDIRECT formulas together to make it reference correctly.

I got a formula that can reference another worksheet:
=INDIRECT("'"&A2&"'!A1:A500")

I got a formula that can reference another range:
=INDIRECT("A"&$C2&":"&"A"&$D2)

But I'm unable to get a formula that can reference another worksheet AND Range.

I'm wanting to write a formula that I can dynamically reference another worksheet, column, and Row (range). Please see attached worksheet for my example.

https://www.dropbox.com/home?preview=2015-06-08+INDIRECT+Formula.xlsx

 

[Jonmo1]

I got a formula that can reference another worksheet:
=INDIRECT("'"&A2&"'!A1:A500")

I got a formula that can reference another range:
=INDIRECT("A"&$C2&":"&"A"&$D2)

The combination of those 2 would go something like

=INDIRECT("'"&A2&"'!A"&$C2&":A"&$D2)

If A2 = "Sheet Name"
C2 = 2
D2 = 4

That would be equivelent to
='Sheet Name'!A2:A4

 

[James02]

DANG.... no wonder ur MVP. That's awesome. I've spent 45 minutes trying to figure this out. Thank you so much!!

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[James02]

This is how I got it to work, bout the exact same as yours. Thanks again so much! =INDIRECT("'"&A2&"'!"&B2&C2&":"&B2&D2)