Indirect Function

[ashokwal]

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

 

[ashokwal]

_Mex2.xls
	A	B	C	D	E	F
1
2
3
4
5
6
7			117031	=INDIRECT(+C7&"!"&"P$137")
8			117251	20.8%
9			117261	31.5%
10			117517	23.2%
2005mix

 

[eliW]

Hi,

In your formula: instead of

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

use:

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

Eli

 

[just_jon]

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)

 

[ashokwal]

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 ,

 

[just_jon]

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

=INDIRECT($C$14&ADDRESS(ROW()+130,COLUMN()+12,2))