"Murray" wrote:

On Jan 30, 3:31 pm, Chad wrote:
I have a workbook that in Sheet3 has a cell with the following formula
=IF(ISBLANK(Sheet1!$A$16),"-",Sheet1!$A$16)

Anyway if I delete row 16 on Sheet 1 and everything else shifts up then this
formula becomes

=IF(ISBLANK(Sheet1!#REF!),"-",Sheet1!#REF!)

Is there anyway to prevent this and make it just pull the data that is now
in Sheet1 A16

You could try using the OFFSET function:

=IF(ISBLANK(OFFSET(Sheet1!A1,15,0)),"-",OFFSET(Sheet1!A1,15,0))

The OFFSET function takes a reference position and then specifies a
number of rows and columns to offset from ie in this case, from cell
A1 offset 15 rows down and zero rows across. Doing it this way allows
you to delete rows and it should still be OK.

HTH

Murray



OK Your OFFSET idea worked wonders on my first workbook but know I have
another one and the same thing need to be done but there are over a 1000
cells i need to change. Is there a macro I could right to find a reference
and change it to the corresponding OFFSET function.

remove $ from $A$16, then it will work.
$ makes the address absolute.

"Chad" wrote:



"Murray" wrote:

On Jan 30, 3:31 pm, Chad wrote:
I have a workbook that in Sheet3 has a cell with the following formula
=IF(ISBLANK(Sheet1!$A$16),"-",Sheet1!$A$16)

Anyway if I delete row 16 on Sheet 1 and everything else shifts up then this
formula becomes

=IF(ISBLANK(Sheet1!#REF!),"-",Sheet1!#REF!)

Is there anyway to prevent this and make it just pull the data that is now
in Sheet1 A16

You could try using the OFFSET function:

=IF(ISBLANK(OFFSET(Sheet1!A1,15,0)),"-",OFFSET(Sheet1!A1,15,0))

The OFFSET function takes a reference position and then specifies a
number of rows and columns to offset from ie in this case, from cell
A1 offset 15 rows down and zero rows across. Doing it this way allows
you to delete rows and it should still be OK.

HTH

Murray



OK Your OFFSET idea worked wonders on my first workbook but know I have
another one and the same thing need to be done but there are over a 1000
cells i need to change. Is there a macro I could right to find a reference
and change it to the corresponding OFFSET function.