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.

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.

Thanks Bernie,

I have used the example of : =INDIRECT("'Sheet 1'!B" & ROW() - 3) to
succesfully allow me to drag down.
Now if I wantewd to then drag across too (i.e.across from column A to column
DB) how can I change the formula?

Andy


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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.

Andrew,

You're welcome. Try this:

=INDIRECT("'Sheet 1'!" & ADDRESS(ROW() - 3, COLUMN() - offset))

HTH,
Bernie
MS Excel MVP

"Andrew Duncan" wrote in message
...
Thanks Bernie,

I have used the example of : =INDIRECT("'Sheet 1'!B" & ROW() - 3) to
succesfully allow me to drag down.
Now if I wantewd to then drag across too (i.e.across from column A to
column DB) how can I change the formula?

Andy


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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.

Another way is to use R1C1 notation in the =indirect() function:

=INDIRECT("'Sheet 1'!r"&ROW()-3&"C"&COLUMN()-offset,FALSE)


Bernie Deitrick wrote:

Andrew,

You're welcome. Try this:

=INDIRECT("'Sheet 1'!" & ADDRESS(ROW() - 3, COLUMN() - offset))

HTH,
Bernie
MS Excel MVP

"Andrew Duncan" wrote in message
...
Thanks Bernie,

I have used the example of : =INDIRECT("'Sheet 1'!B" & ROW() - 3) to
succesfully allow me to drag down.
Now if I wantewd to then drag across too (i.e.across from column A to
column DB) how can I change the formula?

Andy


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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.






--

Dave Peterson