**********************************

COMMENTS

Welcome to the third issue of the Excel Experts E-letter (or EEE), by 
David Hager. EEE is a semi-monthly publication. Feel free to distribute
copies of EEE to your friends and colleagues. 

In honor of this being the 3rd issue of EEE, I have decided to dedicate
this issue to 3D solutions. As you might know, the 3D capabilities of
Excel are lacking in several areas. However, this just provides fertile
ground for growing those workarounds<g>. 


**********************************

TOP EXCEL WEB SITES

For the best in 3D add-in functionality, check out:

http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm

Also, check out the freeware file Make Excel 3D in the CompuServe Excel
forum library for a comprehensive set of 3D custom functions.

**********************************

WORKSHEET FORMULA TIPS

Excel allows you to make 3D formulas based on the following syntax:

Sheet1:Sheet4!A2:B5

However, the functions that can actually use that syntax are limited 
(although not as limited as the Excel documentation would lead you to 
believe). I put together the following list of functions that represent 
most, but probably not all, of those that are 3D-enabled:

AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, 
STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA, SMALL, LARGE, RANK, 
MEDIAN, PERCENTILE, QUARTILE, TRIMMEAN, SKEW, AND, OR, AVEDEV, DEVSQ, 
SUMSQ

BTW, If you create a defined name for this type of 3D range, be sure to
group all of the sheets in that range if you decide to insert any rows
into the area bounded by that range (if you want your 3D range to reflect
the insertion of that row).


Created by David Hager

To make a conditional format based on the value in the previous 
worksheet, create the following defined name formulas.

GlobRef as:

=INDIRECT("rc",FALSE)

which gives the value from the cell it is used in.

PrevShtValue as:

=INDIRECT(INDEX(GET.WORKBOOK(1),GET.DOCUMENT(87)-1)&"!"&ADDRESS(ROW(),
 COLUMN()))

which gives the value from the cell of the same address in the previous
sheet.

Then, combine these in yet another defined name formula.

GTPSV (this cell value is greater than previous sheet value) as:

=GlobRef>PrevShtValue 

which is used as the conditional formatting formula (in Excel 97 and later
versions).


**********************************

POWER FORMULA TECHNIQUES

Created by David Hager

The problem - to make a 3D formula that adjusts in a z-relative manner 
when it is filled across worksheets. Two separate solutions to this problem
that use a similar methodology are shown below. 

Making a 3D Running Total -

This example uses information entered in column A, with the 3D Running Total
formula in column B. 

Define shtPos as:

This formula returns the sheet position of the active sheet as an integer.

=GET.DOCUMENT(87)

Define wsNames as:

This formula returns an array of sheet names in the active workbook.

=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))

Define shtName as:

This formula returns the active sheet name.

=INDEX(wsNames,shtPos)

Note: The string returned from GET.DOCUMENT(76) could have been modified
to obtain the active sheet name, but the method shown above was used since
the modified array of sheet names was available (and needed for another
3D example).

Define RunningTotal as:

=EVALUATE("SUM(Sheet1:"&shtName&"!A"&ROW()&")")+NOW()*0 

This formula returns the sum for the cell in column A for each worksheet
from Sheet1 to the worksheet where the formula resides. For example, if 
the active sheet was Sheet4 and this formula was in B2, this formula 
(entered as =RunningTotal) would be the equivalent of the Excel formula 
=SUM(Sheet1:Sheet4!A2). Since this formula incorporates xlm macro functions, 
it must be forced to recalculate. This is done by using the term NOW()*0, 
since NOW() is a volatile function.

Making a 3D Moving Average -

Define shtNamem2 as:

=INDEX(wsNames,shtPos-2)

This formula returns the sheet name of the worksheet 2 tabs to the left of
the active sheet.

Define MovingAverage as:

=EVALUATE("AVERAGE("&shtNamem2&":"&shtName&"!A"&ROW()&")")+NOW()*0

This formula returns the average of the values in the sheet 2 tabs to the 
left of the active sheet to the active sheet. For example, if the active 
sheet was Sheet4 and this formula (entered as =MovingAverage) was in B2, 
this formula would be the equivalent of the Excel formula =AVERAGE(Sheet2
:Sheet4!A2).


Created by Laurent Longre

The problem - to make a 3D worksheet array formula. What this means is to
create an array representing a z-range (a range across worksheets) that 
evaluates in the formula bar as an array. The 3D range used in Excel, i.e.

Sheet1:Sheet4!A2:B5

does not behave that way. I suspect that nearly everyone on the EEE list 
has tried to do this and found that it was not possible. However, Laurent 
found that it was possible, given some advanced formula tricks. The INDIRECT
function can return a 3D reference if it is operated on by the N function. 
An illustration of this type of formula is shown below.

3D Diagonal Formula - 

=SUM(N(INDIRECT("Sheet"&{1,2,3}&"!"&ADDRESS({1,2,3},{1,2,3}))))

returns the sum of Sheet1!A1, Sheet2!B2 and Sheet3!C3. How does it work?

"Sheet"&{1,2,3}&"!"&ADDRESS({1,2,3},{1,2,3}) evaluates to the array of 
strings.

{"Sheet1!$A$1","Sheet2!$B$2","Sheet3!$C$3"}

When the INDIRECT function operates on this array, the expected array of
values appear (by highlighting in the formula bar and pressing F9), but
for some reason this array cannot be used by Excel functions. The use of
the N function creates an array that can be used, so that the SUM function 
returns the desired result.


**********************************

POWER PROGRAMMING TECHNIQUES

By Myrna Larson and David Hager

Presented below are 3 UDF's (SumProduct3D, SumIf3D, CountIf3D) that 
provide an useful method of returning a variety of information from 3D 
ranges. Each of these functions use a 3D range argument (written as per
the usual Excel protocol) as a string. This string is processed by the
Parse3DRange function, which returns sheet positions and the range argument
in variables that are used by these functions.


 Function SumProduct3D(Range3D As String, Range_B As Range) _
    As Variant


    Dim sRangeA As String
    Dim sRangeB As String
    Dim Sheet1 As Integer
    Dim Sheet2 As Integer
    Dim Sum As Double
    Dim n As Integer

    Application.Volatile

    If Parse3DRange(Application.Caller.Parent.Parent.Name, _
      Range3D, Sheet1, Sheet2, sRangeA) = False Then
      SumProduct3D = CVErr(xlErrRef)
      Exit Function
    End If
    sRangeB = Range_B.Address

    Sum = 0
    For n = Sheet1 To Sheet2
      With Worksheets(n)
        Sum = Sum + Application.WorksheetFunction.SumProduct( _
	.Range(sRangeA), .Range(sRangeB))
      End With
    Next
    SumProduct3D = Sum
  End Function

  '~~~~~~~~~~
  Function SumIf3D(Range3D As String, Criteria As String, _
    Optional Sum_Range As Variant) As Variant

    Dim sTestRange As String
    Dim sSumRange As String
    Dim Sheet1 As Integer
    Dim Sheet2 As Integer
    Dim n As Integer
    Dim Sum As Double

    Application.Volatile

    If Parse3DRange(Application.Caller.Parent.Parent.Name, _
      Range3D, Sheet1, Sheet2, sTestRange) = False Then
      SumIf3D = CVErr(xlErrRef)
    End If

    If IsMissing(Sum_Range) Then
      sSumRange = sTestRange
    Else
      sSumRange = Sum_Range.Address
    End If

    Sum = 0
    For n = Sheet1 To Sheet2
      With Worksheets(n)
        Sum = Sum + Application.WorksheetFunction.SumIf(.Range _
	(sTestRange), Criteria, .Range(sSumRange))
      End With
    Next n
    SumIf3D = Sum
  End Function

  '~~~~~~~~~~
  Function CountIf3D(Range3D As String, Criteria As String) _
    As Variant

    Dim Sheet1 As Integer
    Dim Sheet2 As Integer
    Dim sTestRange As String
    Dim n As Integer
    Dim Count As Long

    Application.Volatile

    If Parse3DRange(Application.Caller.Parent.Parent.Name, _
      Range3D, Sheet1, Sheet2, sTestRange) = False Then
      CountIf3D = CVErr(xlErrRef)
      Exit Function
    End If

    Count = 0
    For n = Sheet1 To Sheet2
        With Worksheets(n)
          Count = Count + Application.WorksheetFunction.CountIf( _
	  .Range(sTestRange), Criteria)
        End With
    Next n
    CountIf3D = Count
  End Function

  '~~~~~~~~~~
  Function Parse3DRange(sBook As String, SheetsAndRange _
    As String, FirstSheet As Integer, LastSheet As Integer, _
    sRange As String) As Boolean

    Dim sTemp As String
    Dim i As Integer
    Dim Sheet1 As String
    Dim Sheet2 As String

    Parse3DRange = False
    On Error GoTo Parse3DRangeError

    sTemp = SheetsAndRange
    i = InStr(sTemp, "!")
    If i = 0 Then Exit Function

    'next line will generate an error if range is invalid
    'if it's OK, it will be converted to absolute form
    sRange = Range(Mid$(sTemp, i + 1)).Address

    sTemp = Left$(sTemp, i - 1)
    i = InStr(sTemp, ":")
    Sheet2 = Trim(Mid$(sTemp, i + 1))
    If i > 0 Then
      Sheet1 = Trim(Left$(sTemp, i - 1))
    Else
      Sheet1 = Sheet2
    End If

    'next lines will generate errors if sheet names are invalid
    With Workbooks(sBook)
    FirstSheet = .Worksheets(Sheet1).Index
    LastSheet = .Worksheets(Sheet2).Index

    'swap if out of order
    If FirstSheet > LastSheet Then
      i = FirstSheet
      FirstSheet = LastSheet
      LastSheet = i
    End If

    i = .Worksheets.Count
    If FirstSheet >= 1 And LastSheet <= i Then
      Parse3DRange = True
    End If
    End With
Parse3DRangeError:
    On Error GoTo 0
    Exit Function

  End Function  'Parse3DRange


**********************************

EXCEL 2000 INFORMATION

Although the ability to manipulate data with worksheet formulas did not
change in Excel 2000, the other data handling features in Excel 2000 
more than made up for this. In fact, IMHO, it is now ready to take on
high-level data intensive corporate projects that previously could only
be accomplished with a variety of tools. Among these are interactive web 
pages, Office Web Components, Pivot Tables and Charts, web queries, a 
greatly enhanced QueryTable object model, and OLAP cube technology. I plan 
to provide examples ofeach of these in upcoming issues (with a little help
from the readership, I hope<g>).

**********************************

Issue No.3 OF EEE (PUBLISHED 15/16Apr1999)
Next issue scheduled for 01MAY1999.
BY David Hager
dchager@compuserve.com