Dynamic referencing of cells from other sheet with Indirect() Function

[MBS]

Hi All,
I want to know , how can we reference cells from other cells with indirect function.

Means, Say I referred cell C2 from sheet2 in sheet1.
Now If I used Indirect function and want to drag cells to right and below in sheet1, so that I can get the values from sheet2.
However as cell reference in indirect function is in text form I can not drag and refer it dynamically.
Hope I managed to explain the issue.
Please suggest the workaround.

Thanks to all.

 

[p45cal]

You could do something like:

=INDIRECT("Sheet2!" & CELL("address", C2))

but I don't see the point, why not just have:

=Sheet2!C2

and copy that down and across?
Or a single cell which spills:

=Sheet2!C2:E5