# INDIRECT Formula

#### James02

##### Board Regular
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

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Jonmo1

##### MrExcel MVP
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

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

#### Jonmo1

##### MrExcel MVP
Glad to help, thanks for the feedback.

#### James02

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

Replies
2
Views
2K
Replies
3
Views
682
Replies
17
Views
389
Replies
11
Views
856
Replies
2
Views
209

1,195,745
Messages
6,011,401
Members
441,613
Latest member
worksux

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back