INDIRECT Function

[Chrissy]

How do I use the indirect function to sum across worksheets?
I have one sheet per month called June, July, August,...May
and want to have another sheet which is the sum of each cell
in these monthly sheets so use

=SUM(June:May!B6)

I want another sheet which is the YTD totals so it needs to be

=SUM(June:XXX!B6)

Where XXX is the current month. Nothing I have tried works
except to use 11 IFs in the formula which makes it long.

What am I missing?

Chrissy.

 

[Myrna Larson]

I could not get INDIRECT to work with a 3-D formula either.


The following VBA function seems to work for me:

Function Eval(TheFormula As String)
Eval = Application.Evaluate(TheFormula)
End Function

After adding a standard module to your workbook and pasting the code there, you can use a
formula like

=Eval("SUM(Juneecember!B6")
=Eval("SUM(June:"&E1&"!B6")
=Eval("SUM(June:"&TEXT(NOW(),"mmmm")&"!B6")

Or, if you always want to do a sum:

Function SUM3D(Sheet1Name As String, Sheet2Name As String, CellRef As String)
Dim F As String
F = "SUM(___:__!_)"
F = Replace(F, "___", Sheet1Name)
F = Replace(F, "__", Sheet2Name)
F = Replace(F, "_", CellRef)
SUM3D = Application.Evaluate(F)
End Function

and call as

=SUM3D("June",E1,"B6")

where the ending month is in E1

 

[Myrna Larson]

Oops. Looks like I missed the closing parenthesis before the closing quote in the examples my
earlier post. Should be

=Eval("SUM(Juneecember!B6)")
=Eval("SUM(June:"&E1&"!B6)")
=Eval("SUM(June:"&TEXT(NOW(),"mmmm")&"!B6)")

 

[Chrissy]

Thanks - something like that will work.

I have not decided exactly which version I will use yet as
I will need to use this lots in the workbook and will consider
carefully which is the most appropriate. I think they may all
be SUM functions but will have to check.

I am trying to convert someone else's spreadsheet to something
more manageable.

Is there a way to make a user defined function non volatile?
If I am going to use this often then I would like it to not recalc
every time something is entered into one cell.

Chrissy.