Formula applied showing as #REF now :(

[szpt9m]

Hi All,

I have added formulas to one of my excel workbook to pull values from other workbooks..

Today when I opened it, I can see all formulas showing, #REF instead of the other workbook reference!!

I have added formula to more than 100 cells..! what may be the reason and how to resolve it??

The formula which i am seeing today is as below

=IFERROR(IF(#REF!="","",#REF!),"")

Please help me to fix this issue!!

Thank you!!

[XOR LX]

Hi,

Has perhaps the name of the workbook or worksheet to which this formula was referring changed? Or perhaps the location of this file?

Regards

[szpt9m]

No Everything is same..

[FRIEL]

If you add a formula to a cell and then copy that formula up the references will move relative to the cell. if one of the references move off the screen you will get the #REF! error. to avoid the you can lock the cell reference by adding a $ before either the column or row.

EG $A$1 will stay in position
$A1 will move with rows but it will stay fixed in the column
A$1 will be fixed to the rows but the column will move with the cell.

this also works with copy/paste

hope this helps

[szpt9m]

Hi Friel,

Are you telling me to have $ sign infront of the cell reference?

I have added formula to first cell and dragged it to remaining cells in that row.. then dragged the first cell to the adjacent cell and then dragged it to the remaining cells in that column and so on..

so if the cell value is $H$30, to drag it to the below cells and adjacent cells, i have changed it as H30.. is this something which is causing this problem??

[szpt9m]

Or someone please give me a formula to copy cells from other other workbook as per below requirement

1. if the cell in workbook A is blank, then cell in the workbook B should also be blank
2. if the cell in workbook A is having error, then cell in the workbook B should be blank
3. if the cell in the workbook A is having a value greater or equal to zero, then the same value to be copied to the corresponding cell in workbook B..

Currently I am using below formula which shows #REF in the formula bar for the workbook A path when i close and open workbook B after sometime.

=IFERROR(IF('C:\Users\username\Desktop\[WorkBookA.xls]Sheet1'!$G336="","",'C:\Users\username\Desktop\[WorkBookA.xls]Sheet1'!$G336),"")

Thank you.