• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Wildcard Sheet Name Dynamic

Hi,

I posted in Questions but I think this more applicable to the VBA section as I think it is very possible it will be needed. I have been taken to the cleaners over this sheet for the past couple days. What I am trying to do is create a sum across with all sheet that have "Resource" in the sheet name. The tricky part is that tabs get added and deleted and it would be great if the formula knew to calculate accordingly. For example, on the "SUMMARY" tab in C9, I want C9 added from all sheets that have "Resource" in the sheet name. And I would need this formula is a lot of the cells in that tab.

I was able to get a wildcard in but if I add in new sheets, it will not pick it up. Conversely, if I delete sheets, I do not want to get an error. Preferably, I would like to dodge VBA but if it must be, that works. I will just make a button for it.

I appreciate any help. Thanks!
 

Attachments

  • New MYP Template (1st Interim).xlsm
    66.5 KB · Views: 5
Hi !​
When the worksheets are contiguous a 3D formula can be applied like for example =SUM('Resource 1100:Resource 1400'!C9)
as a 3d formula just needs the references of the first and the last worksheets of the contiguous block …​
So if those worksheets are not deleted and only new worksheets may be added between them - or deleted whatever - then​
you do not ever need a VBA code, do you ?​
 
Hi Marc,

Thanks for your response. Agreed in theory but I cannot control if or how they are moved. We usually try to keep them in order numerically. So, for example, if we have Resource 1000 added later, that would come before Resource 1100 and throw the whole thing off. Or if Resource 1100 ever went away, we would delete and it would probably have a reference error. That is why I think if we were able to say sum all C9's that have "Resource" in the sheet name, it would be solid. Another option could be for every sheet where A1 contains "Resource," sum c9. OR if we can do a VBA replace or something. I am not sure.

I would just hate to have to manually adjust these every time because I have a lot of contacts that have groups of Resources. Essentially I am checking their work and things have fallen through the cracks before and threw off our projections. Trying to make it more automatic.
 
So according to your attachment you must​
  1. paste the below code to a general module like Module1

  2. edit the SUMMARY C9 cell formula : =WSum("Resource *")
The WSum user defined function has a second optional argument for the range address to sum.​
When omitted like in the above formula the address is automatically added​
from the cell where the formula stands like if the formula is =WSum("Resource *","C9") …​
Code:
Function WSum@(Name$, Optional Zone$)
         Dim Ws As Worksheet, S@
         Application.Volatile
         If Zone = "" Then Zone = Application.ThisCell.Address
    For Each Ws In ThisWorkbook.Worksheets
          If Ws.Name Like Name Then S = S + Ws.Range(Zone).Value2
    Next
         WSum = S
End Function
Do you like it ? So thanks to click on bottom right Like !​
 
Back
Top