# REF! when deleting cells in another spreadsheet
Good Morning,
I have an Excel file with two worksheet, in 'Plan1' all data is 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 'is deleted, all references are lost in' Plan2', showing the error ' #REF!'.
Does anyone know any formula that does not use this reference that leads in consideration the excluded cells? Something where ' = Plan1!A5 'always evening' = Plan1!A5 'same as the' = Plan1!A5 ' has been deleted.
I'm already ahead of myself, the CIF (cipher) does not fit for this.
2 answers
The solution, let's say it's the easiest, was to rewrite the second worksheet with a macro. Since it is kind of static it worked out this 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
$(cipher) is used to fix the position.
If you write = Plan1!A5 and copy this formula to the following columns, it will be replaced by = Plan1!B5, = Plan 1!C5.. and if you copy to the following lines, then they 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 that will remain the same and fixed the row with = Plan1!A$5, or can fix both = Plan1!$A$5. Regardless of where 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.