#REF! when deleting cells in another spreadsheet

Question

Good morning,

I have an Excel file with two spreadsheet, in 'Plan1' all data are deleted, and a MACRO I made, imports a TXT file and populates the 'Plan1', inserting data and calculations. In 'Plan2' the data is fixed and takes as reference the data of 'Plan1', however when the data of 'Plan1' are deleted, all references are lost in 'Plan2', showing the error '#REF!'.

Does anyone know any formula that does not use this reference that takes into account the deleted cells? Something where '=Plan1! A5' will always be '=Plan1! A5' even if '=Plan1! A5' has been deleted.

The $(dollar sign) is not good for this.

Answer 1

The solution, let’s say it’s the easiest, was to rewrite the second spreadsheet with a macro. Since it’s kinda static worked out that way:

Sheets("Plan2").Select
Range("A5").Select
ActiveCell.FormulaR1C1 = _
    "=IF(OR(PLAN1!RC[2]>0,PLAN1!RC[15]>0),PLAN1!RC[1],0)"

#... RESTANTE DO CÓDIGO ...

Range("O5").Select
ActiveCell.FormulaR1C1 = _
    "=IF(RC[-9]=""CC"",RC[-4]*-97.25%,IF(RC[-9]=""CA"",RC[-7]-RC[-4],IF(RC[-9]=""MX"",RC[-7]-RC[-4])))"

Range("A5:O5").Select
#COPIA TODA A LINHA PARA AS DEMAIS
Selection.AutoFill Destination:=Range("A5:O309"), Type:=xlFillDefault
Sheets("PLAN1").Select

Thanks for the help

Answer 2

$(dollar) serves to fix the position.

If you write =Plan1! A5 and copy this formula to the following columns, it will be replaced by =Plan1! B5, =Plan1! C5.. and if copy to the following lines, then it will be replaced by =Plan1! A6, =Plan1! A7....

If you want to fix the column you write =Plan1! $A5 and can copy to other columns it will stay equal and fixed the row with =Plan1! A$5, or you can fix both = Plan1! $A$5. Wherever this formula is copied it will always point to the same place.

But Plan1 is there as a reference and will be replaced by #REF! everywhere it appears as soon as the spreadsheet is removed.