Formula reference to never dissapear

[kalcerro_1]

Hello,

I need help with one question.

I have a formula in a cell: =count.if('4.JiraResult'$D$5:$D$3000;TechAsseRes!K4) but sometimes, the reference sheet: "4.JiraResult" will not always be present and a #¡REF! can appear.
Is there a way to enter a formula, so the formula can be updated when the sheet "4.JiraResult" appear in the current workbook.

The current workbook is working with a macro and I can consider work with a macro if necessary.

Thank you, In advance!
Karin.

 

[mumps]

Try this macro in the code module for ThisWorkbook. When you open the workbook, it will check if the sheet exists. If it does exist, it will place the formula in A1 of "Sheet1". Change the sheet name and range to suit your needs.

Private Sub Workbook_Open()
    If Evaluate("isref('" & "4.JiraResult" & "'!A1)") Then
       Sheets("Sheet1").Range("A1").Formula = "=countif('4.JiraResult'$D$5:$D$3000;TechAsseRes!K4)"
    End If
End Sub

 

[kalcerro_1]

Thank you @mumps very useful answer and in fact is working good. How can I change the code to create the formula in automatic mode (in a specific cell in tab"TechAsseRes" when the tab: "4.JiraResults" appear. I mean, in the module ThisWorkbook works great, but this excel tool is creating the tab "4.JiraResults" in the moment the user is moving on and changing values.

Thank you very much for your support!

Try this macro in the code module for ThisWorkbook. When you open the workbook, it will check if the sheet exists. If it does exist, it will place the formula in A1 of "Sheet1". Change the sheet name and range to suit your needs.

Private Sub Workbook_Open()
    If Evaluate("isref('" & "4.JiraResult" & "'!A1)") Then
       Sheets("Sheet1").Range("A1").Formula = "=countif('4.JiraResult'$D$5:$D$3000;TechAsseRes!K4)"
    End If
End Sub

 

[Candyman8019]

try: =iferror("",countif('4.JiraResult'$D$5:$D$3000;TechAsseRes!K4))

 

[kalcerro_1]

Thank you @Candyman8019
Best regards,

Karin.