Sum only available worksheets

[VegasNath]

I am using formula like:

=SUM('01:31'!E5)

If I delete tab 31, the formula will change to

=SUM('01:30'!E5)

How can I stop the formula from changing? Ideally, I would like the formula to sum E5 for all of the present sheets within the range of 01 to 31, and report 0 if none of those tabs are available. Is this possible?

[HansV]

Insert a blank sheet named Start before sheet 01, and a blank sheet named End after sheet 31.
Hide the sheets Start and End (this is optional).
You can now use a formula such as

=SUM('Start:End'!E5)

Since Start and End are blank, they won't contribute to the sum, but you can delete some or all of the sheets in the range 01...31 without affecting the formula.

You'll have to be careful when inserting new sheets.

[VegasNath]

You know all the tricks.......

[VegasNath]

So, how about a formula that will auto adjust to pick up from the next sheet after 'start' or the last sheet before 'end'.

EG:

If the worksheets were start, 1, 6, 7, ............29, end

the formula would auto adjust to pick up from sheet 1 and another formula that would pick up from sheet 29.

Any magic available here?

[HansV]

The "magic" is called VBA. Enter the following function into a module in the workbook:

Code: Select all

Function GetRelativeValue(SheetName As String, Offs As Integer, Addr As String)
  GetRelativeValue = Worksheets(Worksheets(SheetName).Index + Offs).Range(Addr).Value
End Function

To get the value of cell B5 in the sheet after Begin, use the formula

=GetRelativeValue("Begin",1,"B5")

To get the value of cell D37 in the sheet before End, use the formula

=GetRelativeValue("End",-1,"D37")

[VegasNath]

You never cease to amaze me!

Magic, Thankyou!

[VegasNath]

=GetRelativeValue("End",-1,"D37")

Hans, this works like a dream in normal circumstances, where a new sheet is introduced into the range, before 'end' (using vba). The formula updates as expected. However, if I manually move sheets around within the start:end range and a differend sheet is prior to end, the formula does not update. Calculation is turned on, and F9 does not refresh the formula either. Any idea why?

[HansV]

To force the function to be recalculated, you can add a line:

Code: Select all

Function GetRelativeValue(SheetName As String, Offs As Integer, Addr As String)
  Application.Volatile
  GetRelativeValue = Worksheets(Worksheets(SheetName).Index + Offs).Range(Addr).Value
End Function

If you have a very large number of cells with this function in their formula, this may have a negative impact on performance.

[VegasNath]

Thanks Hans. There are 48 formula's, the impact is not noticeable as far as I can tell.

[HansV]

48 is insignificant - you might notice it if there were thousands...

[VegasNath]

The "magic" is called VBA. Enter the following function into a module in the workbook:

Code: Select all

Function GetRelativeValue(SheetName As String, Offs As Integer, Addr As String)
  GetRelativeValue = Worksheets(Worksheets(SheetName).Index + Offs).Range(Addr).Value
End Function

To get the value of cell B5 in the sheet after Begin, use the formula

=GetRelativeValue("Begin",1,"B5")

To get the value of cell D37 in the sheet before End, use the formula

=GetRelativeValue("End",-1,"D37")

Back again......
This is a very useful function which once set up, takes care of a lot of hard work. It's not easy to create the set-up though, as I have to change the cell reference in each and every cell. Is there a way that I can use a volatile cell reference instead of "B5"..... etc?

[HansV]

You could change the definition of the function to

Code: Select all

Function GetRelativeValue(SheetName As String, Offs As Integer, rng As Range)
  GetRelativeValue = Worksheets(Worksheets(SheetName).Index + Offs).Range(rng.Address).Value
End Function

You can then use it in a formula such as

=GetRelativeValue("Begin",1,B5)

Note the absence of quotes around B5. You can copy this formula down or across, and the cell reference will be adjusted automatically.

[VegasNath]

Great stuff! Thankyou very much Hans.