Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF in formula when delenting source
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF in formula when delenting source
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 "SpeeD72" wrote in 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF in formula when delenting source
-----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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF in formula when delenting source
-----Original Message----- -----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. . ####### 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
#REF in formula when delenting source
Another is to use Indirect
=Indirect("Data!A1") will refer to A1 even after row1 is deleted. -- Regards, Tom Ogilvy "J Morrison" wrote in message ... -----Original Message----- -----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. . ####### 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula based on source = 30% | Excel Discussion (Misc queries) | |||
How to reach at the source of the formula | New Users to Excel | |||
Changing Source Data in a Formula | Excel Discussion (Misc queries) | |||
Move source without altering formula | Excel Discussion (Misc queries) | |||
Travelling from a formula to its source | Excel Discussion (Misc queries) |