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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]cell reference[/TD]
[TD]formula[/TD]
[TD]output[/TD]
[/TR]
[TR]
[TD]Acc!BB2[/TD]
[TD]=indirect(a2)[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]#ref![/TD]
[TD]=indirect(a3)[/TD]
[TD]#ref![/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]....[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

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