Dynamic use of Indirect Formula

[MUKESHY12390]

Hi All -

Is there any way if I can make "D2" dynamic. I mean while pasting it to other Row or other Column "D2" should change to D3 or E2 ( Bascially I want to adjust Relative Reference concept while pasting.)


=INDIRECT("Sheet1!"&"$D2").

Any help would be appreciated.

Thanks!

 

[jasonb75]

That is easiest done with R1C1 references

=INDIRECT("Sheet1!R[1]C[1]",FALSE)

The numbers is square brackets are an offset from the cell with the formula (sheet doesn't matter, only cell position). R[0]C[0] is same row and same column, positive numbers move down or right, negative numbers move up or left.

 

[MUKESHY12390]

Wow!!! it worked thanks!!

Is there any other way just curious.

 

[jasonb75]

You could use something like

=OFFSET(INDIRECT("Sheet1!D2),ROWS(D$1:D1)-1,COLUMNS($D1,D1)-1)

or

=INDIRECT(ADDRESS(ROW(D2),COLUMN(D2),4,1,"Sheet1"))

There might be other ways as well, those are the first that come to mind.

 

[MUKESHY12390]

Thanks again.... Actually I was trying with INDIRECT and Address earlier.

3 solutions are more than enough.