#REF in formula when delenting source

  • Thread starter Thread starter SpeeD72
  • Start date Start date
S

SpeeD72

Hi.

I have done a macro that "uses" that creat PDF´s of a
workshseet (formated table). this formated table as is
source in another worksheet that as a 1000 record
database.

The problem is that, when i delete row 2 (so that the
valeu change in the "formated table") my links in the
other worksheet becomes #REF.

How stop #REF from appering?? i´ve tried manual
calculations baut i was no good....

Thanks´s a lot
SpeeD
 
Depends on what you want to do. What was the information about the PDF
provided for - it doesn't seem to have any significance with reference to
your question.

You need to set up your formula so it the reference isn't affected by the
deletion.

=Indirect("[Data1.xls]Sheet1!A2")

will alway reference the current row 2. This will only work if Data1.xls is
open, however

--
Regards,
Tom Ogilvy


Hi.

I have done a macro that "uses" that creat PDF´s of a
workshseet (formated table). this formated table as is
source in another worksheet that as a 1000 record
database.

The problem is that, when i delete row 2 (so that the
valeu change in the "formated table") my links in the
other worksheet becomes #REF.

How stop #REF from appering?? i´ve tried manual
calculations baut i was no good....

Thanks´s a lot
SpeeD
 
-----Original Message-----
Hi.

I have done a macro that "uses" that creat PDF´s of a
workshseet (formated table). this formated table as is
source in another worksheet that as a 1000 record
database.

The problem is that, when i delete row 2 (so that the
valeu change in the "formated table") my links in the
other worksheet becomes #REF.

How stop #REF from appering?? i´ve tried manual
calculations baut i was no good....

Thanks´s a lot
SpeeD
.
when you delete the cells where the data comes from the
formula will also delete its reference to the cells. look
at using clear contents rather than delete or evluate your
formulae before the deletion.
 
-----Original Message-----

formula will also delete its reference to the cells. look
at using clear contents rather than delete or evluate your
formulae before the deletion.
.
#######

You can also use the offset function in Excel, instead of
directly referring to the cell that will be deleted. Use
offset and make sure the reference cell is one that will
never be in a row that is deleted.
 
Another is to use Indirect

=Indirect("Data!A1")

will refer to A1 even after row1 is deleted.

--
Regards,
Tom Ogilvy

-----Original Message-----

formula will also delete its reference to the cells. look
at using clear contents rather than delete or evluate your
formulae before the deletion.
.
#######

You can also use the offset function in Excel, instead of
directly referring to the cell that will be deleted. Use
offset and make sure the reference cell is one that will
never be in a row that is deleted.
 
Back
Top