dragging of cell with indirect formula

[lekkermarc]

Hello,

I calculated the location of a cell (Acc!BB2). On my output sheet I want to drag the vertical data of this cell just by referring to the reference cell through an indirect function. However, when dragging I only receive the data where the reference cell was calculated but not of the location itself.

cell reference	formula	output
Acc!BB2	=indirect(a2)	2
#ref!	=indirect(a3)	#ref!
...	....	...

<tbody>
</tbody>

How can I construct a formula that drags the reference of an indirect function but not of its location?

Many thanks,

 

[SourabhGupta]

that means you want Acc!BB3 then Acc!BB4 and so on......
assuming we have the cell reference in cell A1, use the following formula
=INDIRECT(SUBSTITUTE($A$1,2,ROW(A3)))
now see what it does--
in cell A1 it will substitute 2 by ROW(A3) which is 3
so it will become Acc!BB3 and then it will give us the indirect of Acc!BB3
when we will copy it down it will replace 2 by Row(A4) which is 4 so it will become Acc!BB4 and so on
Hope it helped!!!
Ask for any help
Thanks