REF# error

[cicada]

Hi all,
I am having a problem with above error code appearing in many cells on my workbook. The workbook has tables filled with data via a dde link. I have been working on formulas (that reference cells in the table ranges) and testing them they were ok prior to saving if there are no values for the given formulas to calculate it returns DIV# i think because it is dividing by zero this poses no problem. After saving the file however and I go back into it many of the cells have REF# error. If i try copying them over to another workbook the reference error appears again, never really encountered this before though i am a newb.
Any ideas or any questions i'm all ears.

thanks
Dan

 

[JazzSP8]

Hi Dan

Not had much experience connecting to a DDE link but you normally get #REF errors when the formula is referencing something that doesn't exist anymore, if you'd deleted a column or something for example; you might want to check that your external links to other data sources are still open.

The #DIV/0 error is indeed because you're trying to divide by zero BTW

HTH

 

[cicada]

Hi jazz,

all the formuals reference ranges that are filled by an external dde link. When i reopen the workbook some have DIV# and some show REF#. When i reestablish the dde link the DIV# cells calculate however the REF# cells don't so i'm not sure why some, which reference data from the same tables work and the others continue to show REF#. Any ideas, I think it must be some trick with dde links.

Thanks again
Dan

 

[JazzSP8]

If the link has been broken then your cell references in your formula will also show #REF where the cell location was and therefore won't fix themselves when the link is remade; you might try and get round this by using the INDIRECT formula but that could make calculation slow if you use it a lot.

 

[cicada]

thanks for the reply again jazz,

I would have thought the same thing if it were not for some formulas that also reference ranges to the same data, which do not return an REF# error. instead they continue to show DIV# and when the link is reestablished work fine. so I fail to see why the two are being distinguished by excel. Does anyone have any thoughts on this.

Regards
Dan