Linking a relative formula to another worksheet problem

[Guest]

Let me know what I am missing...
1. I use this formula =IF(+'Order Journal'!B16="", (""),(+'Order
Journal'!B16)) to reference a cell on another worksheet.
2. On the 'Order Journal' sheet I delete out the row 16.
3. the formula does not continue to reference B16 -which is what I want it
to do- instead it gives me a #REF error - =IF(+'Order Journal'!#REF!="",
(""),(+'Order Journal'!#REF!))
4. I thought if the formula was relative it would always reference B16 no
matter what cell, row, column was deleted on that worksheet.
thanks in advance for help. Ricky

 

[Peo Sjoblom]

No it will not and I don't see the logic behind it, what if you delete a row
by mistake, this error will tell you that. If you want to always point to
the same cell
you can use

=INDIRECT("'Order Journal'!B16")

will always point to that cell

 

[JE McGimpsey]

Absolute or relative references both follow the actual referenced cells.
Relative references will change when the formula in the referencing cell
is copied (e.g., if you have

A1: =Sheet1!B10

then copying that to A2 yields

A2: =Sheet1!B11

To reference B16, one way:

=IF(INDIRECT("'Order Journal'!B16")="", "", INDIRECT("'Order
Journal'!B16"))

Note that your +'s are superfluous and you don't need the parens around
the nullstring