# Indirect Function

#### ashokwal

##### New Member
I am working in the same workbook.

I have a sheetname 117031 with P137 = 90
Cell c7 is the sheet name which is 117031
When I use the below formula , I get the result as 90

How can I make the p137 cell dynamic i.e when I copy it down , it changes t0 P138, P139

c7, c8
117031 =INDIRECT(+C7&"!"&"P\$137") = 90

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
_Mex2.xls
ABCDEF
1
2
3
4
5
6
7117031=INDIRECT(+C7&"!"&"P\$137")
811725120.8%
911726131.5%
1011751723.2%
2005mix

Hi,

(+C7&"!"&"P\$137")

use:

(+C7&"!"&"P"&ROW()+130)

Eli

ashokwal said:
I am working in the same workbook.

I have a sheetname 117031 with P137 = 90
Cell c7 is the sheet name which is 117031
When I use the below formula , I get the result as 90

How can I make the p137 cell dynamic i.e when I copy it down , it changes t0 P138, P139

c7, c8
117031 =INDIRECT(+C7&"!"&"P\$137") = 90

You can use the row value. If he 1st instance of the formula - the one where you want 137 to be - is say row 7, then --

=INDIRECT(C7&"!P" & ROW()+130)

Thanks to Eli & Just Jon
It is super.

******************************************************

=INDIRECT(\$C\$14&"!P" & ROW()+7)

What is the syntax for making the sheet name absolute & cell address relative
ie
sheet1!p137 , sheet1!q137 ,
sheet1!p138 , Sheet1!q138 ,

ashokwal said:
Thanks to Eli & Just Jon
It is super.

******************************************************

=INDIRECT(\$C\$14&"!P" & ROW()+7)

What is the syntax for making the sheet name absolute & cell address relative
ie
sheet1!p137 , sheet1!q137 ,
sheet1!p138 , Sheet1!q138 ,

If you want the column ref to shift as you copy sideways, a cheesey way of doing it is to put column letters in an unused row and reference that.

Another way is the ADDRESS function, working off the ROW() and COLUMN() functions.

This

Replies
9
Views
377
Replies
4
Views
168
Replies
1
Views
191
Replies
2
Views
155
Replies
6
Views
305

1,207,255
Messages
6,077,311
Members
446,278
Latest member
hoangquan2310

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