#Ref! Error

[Guzzlr]

Hello
I am getting the #Ref! Error because I have a formula in one sheet, that is linked to another sheet that I delete.
When I replace the deleted sheet with an identical sheet with new data, I get the #Ref! error.
How can I retain the formula that is linked to the deleted sheet, when an identical (same column and data only with new data) sheet is placed so the original formula can see it again?
Thank you

 

[Trixterz]

Use the indirect function within your formulas when linking sheets and ranges. As long as the new sheet has the same name as your old sheet shouldn't cause a #Ref within your formula.

 

[Guzzlr]

Use the indirect function within your formulas when linking sheets and ranges. As long as the new sheet has the same name as your old sheet shouldn't cause a #Ref within your formula.

I'm playing around with the indirect function suggested, and it is returning the #Ref! error.

I have:

=INDIRECT(COUNTIFS('PM Schedule Repair'!$D:$D,Matrix!$B6,'PM Schedule Repair'!$F:$F,Matrix!E$5,'PM Schedule Repair'!$G:$G,Matrix!$D$5))

The count if criteria ranges are correct. I'm not sure if the indirect function will work with CountIfs?
or I'm using it incorrectly.
Thanks for the help

 

[Guzzlr]

I'd suggest you use code to repopulate the formulas.

My code for CountIfs:

Option Explicit
Dim Rng As Range

Sub Countifs()

Worksheets("Matrix").Select
With Selection
    Rng("E6").FormulaR1C1 = "=COUNTIFS('PM Schedule Repair'!$D:$D,Matrix!$B6,PM Schedule Repair'!$F:$F,Matrix!E$5,'PMSchedule Repair'!$G:$G,Matrix!$D$5)"
End With

End Sub

However, I am doing something wrong, as I get the below error.
I'm sure it is a simple error...
can you lead me in the right direction
Thank you

 

[RoryA]

You didn't assign a range to the Rng variable. Perhaps you meant:

Worksheets("Matrix").Range("E6").Formula = "=COUNTIFS('PM Schedule Repair'!$D:$D,$B6,PM Schedule Repair'!$F:$F,E$5,'PMSchedule Repair'!$G:$G,$D$5)"