#REF! When you exclude cells in another worksheet

Question

#REF! When you exclude cells in another worksheet

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

Good morning,

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

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

I'm already ahead, $ (dollar sign) is not good for that.

excel office

asked by anonymous 20.05.2016 / 15:34

2 answers

Javascript - Problem in function that adds textbox SQL - How to select or delete a column with a value of null in a query select

score 2 · Answer 1

The solution, let's say it's the easiest, was to rewrite the second worksheet with a macro. Since it is a bit 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

score 1 · Answer 2

$ (dollar sign) serves to fix the position.

If you type = Plan1! A5 and copy this formula to the following columns, it will be replaced by = Plan1! B5, = Plan1! C5 .. and if copied 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 you can copy it to other columns that will remain the same and fix the line with = Plan1! A $ 5, or you 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 for reference and will be replaced by #REF! everywhere it appears as soon as the spreadsheet is removed.