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.

 

[redbug]

You could try this (from help)

1. Select the cells that contain the error value.
2. On the Format menu, click Conditional Formatting.
3. In the box on the left, click Formula Is.
4. In the box on the right, type =ISERROR(reference), where reference is a reference to the cell that contains the error value.
5. Click Format, and then click the Font tab.
6. Click Format.
7. In the Color box, select white.

 

[pedie]

RedBug! thanks for the trick but i think it just format and make it look blank but it really is not...can we make it show 0 so that it does not give error to other formulas that are referenced to this cell?

Thanks again for helping.
Pedie

 

[shg]

Maybe =IFERROR(SUM(A1,Sheet2!A1,Sheet3!A1,Sheet4!A1,Datas!A1), 0)

Or, if the sheets appear sequentially,

=IFERROR(SUM(Sheet1:Datas!A1), 0)

 

[pedie]

Maybe =IFERROR(SUM(A1,Sheet2!A1,Sheet3!A1,Sheet4!A1,Datas!A1), 0)

Or, if the sheets appear sequentially,

=IFERROR(SUM(Sheet1:Datas!A1), 0)

Shg, thanks alot! i tried this earlier and then when i hit F2 and then hit enter it give dailog box pop up....which is okay but what want to confirm is if there is no sheet called datas but gets added latter will the formula be auto calc.?

and '=IFERROR(SUM(Sheet1:Datas!A1), 0) and does this means that sheet1 to sheet5 and Datas next to it, will it sum all A1 from different sheets between Sheet1 to sheets("datas")?

Thanks again for helping!

 

[pedie]

Shg, thank you soo much that solves my problem...I mean your 2nd formula...


Thanks again!

Thanks Redbug!

 

[pedie]

=IFERROR(SUM(Sheet1:Datas!A1), 0)

Hi Shg, 'm trying to use formula you suggested with Indirect
=SUM(Sheet1:INDIRECT(B1&"A1") where B1 value is "Datas!" but i dont know why it returns #names? error...


Could someone please check and help me out here...

 

[Domenic]

I don't think SUM will accept a 3D reference via INDIRECT. Try a custom function instead. Maybe...

[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], 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] 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 + Worksheets(i).Range(Cell.Address)
    [color=darkblue]Next[/color] i
[color=darkblue]End[/color] [color=darkblue]Function[/color]
[/font]

The worksheet formula would be as follows...

=MySum("Sheet1","Sheet3",A2)

Note, however, unlike SUM, the sheet name referenced in this custom function will not automatically change if the sheet name is changed via the tab.

 

[pedie]

I don't think SUM will accept a 3D reference via INDIRECT. Try a custom function instead. Maybe...

The worksheet formula would be as follows...

=MySum("Sheet1","Sheet3",A2)

Note, however, unlike SUM, the sheet name referenced in this custom function will not automatically change if the sheet name is changed via the tab.

Hi, Domenic....thanks alot! thank you...
how can i use/reference the sheet name in cell like =MySum("Sheet3",indirect(B1),A2)


Thanks again!

 

[pedie]

Domenic, thank you very much!!! it works perfect!!!