Summing same cell where work sheet contains...

[menor59]

I have the following formula:

='Quote 1'!$D65+'Quote 3'!$D65+'Quote 2'!$D65+'Quote 4'!$D65+'Quote 5'!$D65

Is there a way to have it do Something along the lines of

=SUM(Quote*$d65)

basically Look at any sheet that is named Quote and sum those Cells up?

 

[AlphaFrog]

You could try this...

=SUM('Quote 1:Quote 5'!D65)

Note:it will sum all sheets in between Quote 1 and Quote 5 inclusive regardless of their sheet names. It is positional and not name based.

 

[menor59]

I thought of that...but theoratically others could have Quote 6, 7 etc.etc...I need to Capture Quote * or a variant there of...thoughts?

 

[AlphaFrog]

Could you have a dummy "End Sheet" and have all your quote sheets in between?
=SUM('Quote 1:End Sheet'!D65)

Or it could be done with VBA code if you're up for that?

 

[menor59]

Alpha,

Thanx, That wont work either...

I have 12 sheets labeled...

Quote 1, Quote 2, Quote 3,Quote 4, Quote 5, CO 1, CO 2, CO 3, CO 4, CO 5, Turnover, Totals, DataQuote, DataCO.

So for This discussion...I was hoping to Get the Formula to work for Quote*, then i would have put in in a different cell to Look at only CO*. I hate to say it but it needs to be wildcarded....\

Thoughts?

 

[AlphaFrog]

There's probably a clever way to do it with just a formula but it's beyond me.

Here's a custom UDF function. Put the code in a standard macro code module. Then enter a formula like this
=SUMQUOTES(D65)

[COLOR=darkblue]Function[/COLOR] SUMQUOTES(Rng [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ws As Worksheet
    Application.Volatile
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] Worksheets
        [COLOR=darkblue]If[/COLOR] LCase(ws.Name) [COLOR=darkblue]Like[/COLOR] "quote*" [COLOR=darkblue]Then[/COLOR]
            SUMQUOTES = WorksheetFunction.Sum(SUMQUOTES, ws.Range(Rng.Address))
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] ws
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

 

[menor59]

Thank You Sir!

 

[AlphaFrog]

This will allow you to specify a base sheet name with wildcard.
=SUMSHEETS("Quote*",D65)

[COLOR=darkblue]Function[/COLOR] SUMSHEETS(strBase [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], Rng [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ws As Worksheet
    Application.Volatile
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] Worksheets
        [COLOR=darkblue]If[/COLOR] ws.Name [COLOR=darkblue]Like[/COLOR] strBase [COLOR=darkblue]Then[/COLOR]
            SUMSHEETS = WorksheetFunction.Sum(SUMSHEETS, ws.Range(Rng.Address))
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] ws
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

 

[menor59]

Wasnt the One previous the same thing?

 

[AlphaFrog]

No. SUMQUOTES only summed the "Quote*" sheets. The base sheet name was fixed in the code.
=SUMQUOTES(D65)

The SUMSHEETS function allows you to set the base name in the formula. So if you wanted to sum the CO sheets, then...
=SUMSHEETS("CO*",D65)