Is there a way to avoid #Ref error when the sheet/ref does not exist/is removed?

[pedie]

Hi, is there a way to avoid #Ref error when the sheet/ref does not exist/is removed?
kind of formula i'm referring to
'=SUM(A1,Sheet2!A1,Sheet3!A1,Sheet4!A1,Datas!A1)

When sheets "datas" for example is removed/deleted it errors back as #REF

Thanks in advance for helping.

 

[pedie]

Hi, Domenic, thanks alot for helping me out in this thread...that was very important to me and need quick assistance and you help me out! thanks alot again...

I know....it is too much of me to go on asking questions for free but can you please write same kind of code for average and not sum?


Thanks & regards,
Pedie

 

[Domenic]

how can i use/reference the sheet name in cell like =MySum("Sheet3",indirect(B1),A2)

I'm assuming that you simply replaced INDIRECT(B1) with B1, where B1 contains the sheet name, correct?

I know....it is too much of me to go on asking questions for free...

Not as far as I'm concerned. As far as I know, there's no limit as to the number of questions one can ask. I say ask away.

...can you please write same kind of code for average and not sum?

Try...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Function[/color] MyAverage(FirstSht [color=darkblue]As[/color] [color=darkblue]String[/color], LastSht [color=darkblue]As[/color] [color=darkblue]String[/color], Cell [color=darkblue]As[/color] Range) [color=darkblue]As[/color] [color=darkblue]Double[/color]
    [color=darkblue]Dim[/color] i       [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Cnt     [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Temp    [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] MySum   As [color=darkblue]Double[/color]
    Application.Volatile
    [color=darkblue]If[/color] Worksheets(LastSht).Index >= Worksheets(FirstSht).Index [color=darkblue]Then[/color]
        [color=green]'Do nothing[/color]
    [color=darkblue]Else[/color]
        Temp = FirstSht
        FirstSht = LastSht
        LastSht = Temp
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]For[/color] i = Worksheets(FirstSht).Index [color=darkblue]To[/color] Worksheets(LastSht).Index
        MySum = MySum + Worksheets(i).Range(Cell.Address)
        Cnt = Cnt + 1
    [color=darkblue]Next[/color] i
    MyAverage = MySum / Cnt
[color=darkblue]End[/color] [color=darkblue]Function[/color]
[/font]

 

[pedie]

Domenic, perfect....!!! thanks you very very much!
Great...! now will be really easy of me to take care of things...

Please let me know if i need to keep anything in mind besides renaming sheet from tab...
And i need to make other users to enable the macro for this function to work...so 'm thing i'll make .xlam workbook and place this in addin instead.... so that users dont have to enable the macro for this code to work...


Thanks again Domenic!!!

Function[/COLOR] MyAverage(FirstSht As String, LastSht As String, Cell As Range) As Double

Dim i As Long
Dim Cnt As Long
Dim Temp As String
Dim MySum As Double
Application.Volatile
If Worksheets(LastSht).Index >= Worksheets(FirstSht).Index Then
'Do nothing
Else
Temp = FirstSht
FirstSht = LastSht
LastSht = Temp
End If
For i = Worksheets(FirstSht).Index To Worksheets(LastSht).Index
MySum = MySum + Worksheets(i).Range(Cell.Address)
Cnt = Cnt + 1
Next i
MyAverage = MySum / Cnt
End Function
[/CODE]

 

[Domenic]

You're very welcome! Glad I could help!

 

[shg]

Here's another way, but it will give different answers than Domenic's if there are blank cells or text in the range.

Function MyAverage(sSht1 As String, sSht2 As String, ByVal vRng As Variant) As Double
    Application.Volatile
    
    If TypeOf vRng Is Range Then vRng = vRng.Address
    MyAverage = Application.ThisCell.Worksheet.Evaluate("average(" & sSht1 & ":" & sSht2 & "!" & vRng & ")")
End Function

E.g.,

=MyAverage("Sheet1", "Sheet3", "A1:B2")

or

=MyAverage("Sheet3", "Sheet1", A1:B2)

 

[pedie]

Shg, thanks again for helping...!!!
I think i'll have a sweetdream tonight...


Good day to you guys!....
me, 'm going to sleep!

Thanks alot again for helping

 

[Domenic]

shg,

Nice one!

Pedie,

It looks like shg has given you a better solution. In any case, in the interest of completeness, my first function can be amended to allow formula blanks and a multi-cell range, as follows...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Function[/color] MySum(FirstSht [color=darkblue]As[/color] [color=darkblue]String[/color], LastSht [color=darkblue]As[/color] [color=darkblue]String[/color], Rng [color=darkblue]As[/color] Range) [color=darkblue]As[/color] [color=darkblue]Double[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Temp As [color=darkblue]String[/color]
    Application.Volatile
    [color=darkblue]If[/color] Worksheets(LastSht).Index >= Worksheets(FirstSht).Index [color=darkblue]Then[/color]
        [color=green]'Do nothing[/color]
    [color=darkblue]Else[/color]
        Temp = FirstSht
        FirstSht = LastSht
        LastSht = Temp
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]For[/color] i = Worksheets(FirstSht).Index [color=darkblue]To[/color] Worksheets(LastSht).Index
        MySum = MySum + Application.Sum(Worksheets(i).Range(Rng.Address))
    [color=darkblue]Next[/color] i
[color=darkblue]End[/color] [color=darkblue]Function[/color][/font]

 

[Domenic]

shg,

Is there any reason why you're using...

MyAverage = Application.ThisCell.Worksheet.Evaluate("average(" & sSht1 & ":" & sSht2 & "!" & vRng & ")")

instead of

MyAverage = Application.Evaluate("average(" & sSht1 & ":" & sSht2 & "!" & vRng & ")")

?

 

[shg]

With Application.Evaluate, if you switch to another workbook and do something that causes Excel to calculate, the formula will evaluate in the context of the active sheet. Then when you come back to the workbook with that formula, it will have a #VALUE! error (or spurious results) until it recalculates.

The option of passing the range as a string address avoids a circular reference error if in Sheet1!A1 you have the formula

=MyAverage("Sheet2", "Sheet3", A1)

 

[Domenic]

With Application.Evaluate, if you switch to another workbook and do something that causes Excel to calculate, the formula will evaluate in the context of the active sheet. Then when you come back to the workbook with that formula, it will have a #VALUE! error (or spurious results) until it recalculates.

The option of passing the range as a string address avoids a circular reference error if in Sheet1!A1 you have the formula

=MyAverage("Sheet2", "Sheet3", A1)

That's great! Thanks shg, much appreciated!

Edit: I saw your edit. Thanks again, shg!