Indirect Function

Hi Stew,

I don't believe the INDIRECT function can work with a 3-d range like this. If you are willing to accept a simple user-defined function (UDF) here is one that sums a cell across adjacent worksheets:

Function SumX(WS1 As String, WS2 As String, Cell As String) As Double
'Sums Cell from worksheets WS1 to WS2
'Example: =SUMX("Jan","Aug","B4")
' sums the value of cell B4 on worksheets named Jan through Aug
Dim iWsh As Integer
SumX = 0
For iWsh = Worksheets(WS1).Index To Worksheets(WS2).Index
SumX = SumX + Worksheets(iWsh).Range(Cell)
Next iWsh
End Function

To use this function for your situation, the call would be:

=SUMX(A1,A2,"C5")

If you don't know how to install a VBA macro, it's really
quite easy. Just follow these steps:

1) Go to the Visual Basic Editor (VBE). Do this from Tools >
Macro > Visual Basic Editor (or simply keyboard Alt-TMV)

2) In the VBE create a new Macro Module: Insert > Module.
An empty code window pane will appear (Alt-IM).

3) Paste the code into this window. The macro or function is
now available for use from Excel. If it is a Function-type
macro (a UDF) you can immediately use it as an Excel function. If
it is a Sub (subroutine) type macro you can run it from
the Excel Tools > Macro menu (Alt-TMM).

Here is something you can try:

Cell Address -----> Content -----> Value
B1 -----> Cell Addr in each tab -----> A2
B2 -----> Start Month -----> February
B3 -----> End Month -----> August
B4 -----> Summation Formula -----> "=SUM(E2:E13)"

Cells C2 thru C13 January, February.. till December

Cells D2 thru D13 1,2,3 thru 12 (month Numbers).

Paste the following formula in E2 and copy it down the column thru to E13:

=IF(NOT(OR(D2>VLOOKUP($B$3,$C$2:$D$13,2,FALSE),D2<VLOOKUP($B$2,$C$2:$D$13,2,FALSE))),INDIRECT(CONCATENATE(C2,"!",$B$1)),0)

Create a spreadsheet file with tab names as in Cells C2 thru C13 viz. January, February .. till December.

Test Values "A2" in Cell B1
"March" in B2 and "June" in B3.

Total of all values in Cells A2 in tabs March, April, May and June will be in B4.

In Column E actual extracted values will be found against months March thru June, rest will be 0.

Hope this helps.

Cheers!

Uttam

_________________
Everything in this place is 'wanted yesterday'.

So, if you want something today, 'come tomorrow'.
This message was edited by u0107 on 2002-12-19 17:38

Thanks Damon & Uttam for your replies.

I went with Damon's option in the end as the formula is contained in one cell.

This has saved me a lot of time.

Thanks

Stewart