read data from a cell absolutely

[Andrew Duncan]

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.

 

[Bernie Deitrick]

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 )

 

[Andrew Duncan]

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]

Andrew,

You're welcome. Try this:

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

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

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

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