Use tab name in formula

[julhs]

Want formula to sum the same cell across multiple unknown number of sheets where the last 5 digits of the sheet names match the last 5 of the active sheet

Tab names=
Jane 16-17
Fred 17-18
Joe 17-18
Mary 17-18
Fred 18-19
Joe 18-19
Mary 18-19

Active sheet = Joe 17-18

Cell A1 on Active sheet = Sum(Joe17-18,D1+Fred 17-18,D1+Mary 17-18,D1)

 

[Eric W]

Welcome to the forum.

That can't be done with native Excel formulas, although you can do it with a UDF (User-Defined Function). If you want to try that, open a copy of your workbook, right click on the sheet tab on the bottom and select View Code, then from the VBA Editor menu select Insert > Module, then paste this code on the window that opens:

Public Function MultiAdd(ByVal CellAddress As String, ByVal CurSheet As String)
Dim sh As Worksheet

    For Each sh In Worksheets
        If Right(sh.Name, 5) = Right(CurSheet, 5) Then
            MultiAdd = MultiAdd + sh.Range(CellAddress).Value
        End If
    Next sh
    
End Function

Press Alt-Q to close the VBA editor. Now you can enter this function in A1:

=MultiAdd("D1","Joe 17-18")


Of course you see that you have to enter the sheet name in the parameter list. You can get around that by entering this version of the formula:

=multiadd("D1",CELL("filename",INDIRECT("D1")))

The CELL function will return the name of the file, and the sheet name. The UDF just looks at the last 5 characters, so it should work. But be advised that CELL will not work if you haven't saved the file yet. Also CELL doesn't update when you'd expect so it could give suspect results. Also, I used INDIRECT so that you could copy/paste the formula to other sheets.

Hope this helps.

 

[julhs]

Thanks, will give feed back later as not run it yet. Function nicely explained as well which is always helpful

 

[Rick Rothstein]

Public Function MultiAdd(ByVal CellAddress As String, ByVal CurSheet As String)
Dim sh As Worksheet

    For Each sh In Worksheets
        If Right(sh.Name, 5) = Right(CurSheet, 5) Then
            MultiAdd = MultiAdd + sh.Range(CellAddress).Value
        End If
    Next sh
    
End Function

Press Alt-Q to close the VBA editor. Now you can enter this function in A1:

=MultiAdd("D1","Joe 17-18")

Of course you see that you have to enter the sheet name in the parameter list.

If by "active sheet" the OP meant the sheet the formula is on, then your function can get around having to specify that sheet's name this way...

Public Function MultiAdd(ByVal CellAddress As String)
  Dim sh As Worksheet

  For Each sh In Worksheets
    If Right(sh.Name, 5) = Right(Application.Caller.Parent.Name, 5) Then
      MultiAdd = MultiAdd + sh.Range(CellAddress).Value
    End If
  Next
    
End Function

 

[julhs]

Rick

Yes that is what I meant. By not specifying sheet name in formula will mean no editing is required of formula on the sheet template

Thanks

 

[julhs]

I am getting a #Value error in A1, so going to need some more help to resolve it.

However wanted to attach screen shot but struggling to do so, have MrExcel HTML Maker installed as Addin but not able to actually paste anything of any sort into the message.
Has anyone suggestions as to why?

 

[julhs]

Need some further help on this as getting a #Value error in A1

For life of me I cant attach a screenshot of WB so just going to lay it out as best I can below.

Named sheets in Workbook & values in D1 on each sheet =
……… fred 17-18 (D1=£5.00)
……….joe 17-18 (D1=£10.00)
…....mary 17-18 (D1=£15.00)
….……fred 18-19 (D1=£25.00)
…….mary 18-19 (D1=£30.00)

Sum in A1 of sheets:
…....fred 17-18
….....joe 17-18
…...mary 17-18

should equal value of:
.....fred 17-18,D1
+ joe 17-18,D1
+ mary 17-18,D1
..so A1 on all 3 should show £30

Sum in A1 of sheets:
…....fred 18-19
…...mary 18-19

should equel value of:
.....fred 17-18,D1
+ mary 17-18,D1
..so A1 on both should show £55.00

Any help greatly appreciated

 

[Eric W]

I'm not sure exactly what's going on, but I have a few thoughts.

First, you may want to change the UDF to:

Public Function MultiAdd(ByVal CellAddress As String)
  Dim sh As Worksheet

  Application.Volatile
  For Each sh In Worksheets
    If Right(sh.Name, 5) = Right(Application.Caller.Parent.Name, 5) Then
      MultiAdd = MultiAdd + sh.Range(CellAddress).Value
    End If
  Next
    
End Function

The line in red will make it so that it updates when the value cells change.

Next, one reason you can get the #VALUE error is if the function tries to add up non-numeric values. Is the £ symbol in your example an actual character, which would cause the problem, or are the numbers in D1 actual values, and the £ symbol comes from formatting the cell as currency?

 

[julhs]

The £ sign is formatting, I've removed the space and hyphen and add a zero so 'fred 17-18' changed to fred17180' for testing purposes but result still the same.

This is the formula that is in A1 ???
=multiadd("D1",CELL("filename",INDIRECT("D1")))

 

[Eric W]

Ah, that's the problem! With Rick's suggestion, you don't need the second parameter. Using the UDF from post 8, the sheet formula should be:

=multiadd("D1")

The dash in the sheet name shouldn't matter.