Replace harcoded column # with & CELL("row")

=INDIRECT("'" & INDIRECT(LEFT(ADDRESS(1, CELL("col"),4,TRUE),1) & "7") & "'!D" & CELL("row") )

>That is excellent, thank you. If it is not optimal it is plenty good enough.
>
>I had made one mistake in the description of the problem, though (sorry). I should have said:
>
>If the formula is in cell S10, the formula should be =INDIRECT("'" & S7 & "'!D10"). (The name of the worksheet is always in row 7 of the same column where the formula is, and the row from where the data is extracted should be the same row where the formula resides).
>