Andrew,

=INDIRECT("'Sheet 1'!B3")

or, to make it easier to copy down:

=INDIRECT("'Sheet 1'!B" & ROW() + offset)
For example:
=INDIRECT("'Sheet 1'!B" & ROW() - 3)

Or, to not be tied to a specific sheet name or column (in case you rename the sheet or move the
column)

=INDEX('Sheet 1'!B:B,ROW() + offset)

like

=INDEX('Sheet 1'!B:B,ROW()-2 )

--
HTH,
Bernie
MS Excel MVP


"Andrew Duncan" wrote in message
...
Is there a way of referencing a cell in so far as its co-ordinate rather than the typical B3 etc?

I am linking sheets to each other, for example
Cell B2 on Sheet 2 reads Cell B2 on Sheet 1 and continues down the sheet.(B3 to B3, B4 to B4 etc)

If I then delete row B3 from Sheet 1 (deleting the row not just the contents) I will then get
error messages ( #Ref) in Cell B3 on Sheet 2 .

How can I ensure that the Cell B3 on Sheet 2 reads what has become B3 on Sheet 1 ?

I know in VB there is a co-ordinate or referencing of cells by the number of columns / rows from a
datam, but is there a similiar process in Excel ?


Thanks.