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

COMMENTS

Welcome to the 18th issue of the Excel Experts E-letter (or EEE), by 
David Hager. EEE is now a monthly publication. Feel free to distribute
copies of EEE to your friends and colleagues and to contribute your Excel 
gems to EEE so that others can benefit from your work. 

Back issues are available for download from the EEE web page located on 
John Walkenbach's web site. New issues are normally available on the 1st 
of each month. There will be periods when EEE is not published due to time
and travel constraints.

http://www.j-walk.com/ss/excel/eee/index.htm

IMPORTANT NOTE!!!

I have just discovered that my mailing list has suffered from tremendous 
problems. A significant number of people on the list have been receiving
multiple copies of EEE and over 100 people have been somehow kicked off of
the list over a period of months. The software I am using to manage the 
list is old and obviously contains bugs I was not aware of. I have put in
a significant amount of time to try to correct this problem. I apologize
to all of the people that have not been receiving EEE. I hope that this fix
solves the problem.
 
**********************************
Top Excel Sites

For a list of fixed problems in Microsoft Office 2000 Service Release 1
(SR-1), go to:


http://support.microsoft.com/support/kb/articles/q245/0/21.ASP

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

WORKSHEET FORMULA TIPS

by Bernie Deitrick

<QUESTION/>

I have a formula

=COUNTIF('Sheet1'!Ddd2346, "=0")

in a cell, where Ddd2346 refers to a named range.

What I would like to do is to have an easy way to copy this formula down
a column of cells, and have the Ddd2346 number increment by one each
time.  The next cell needs to be Ddd2347, then Ddd2348 etc.

</QUESTION>

<ANSWER/>

In this specific case, use:

=COUNTIF(INDIRECT("Ddd"&2345+ROW(A1)),"=0")

When this formula is filled down the column, the numeric suffixes of the
named ranges increment by one.

</ANSWER>

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

POWER FORMULA/FUNCTION TECHNIQUES

by George Simms

<QUESTION/>

I have the following problem:

In cells A1:E1:   1, 2, 3, 4, 5
In cells A2:E2:   6, 7, 8, 9, 10

In cells A5:E5:   21, 22, 23, 24, 25

Now I want to total diagonally (A1, B2, C3, D4 and E5) and I do that by
the formula:
=SUM((ROW(A1:A5)=COLUMN(A1:E1))*(A1:E5)) - array entered.

This works fine (sum =65), but I wondered how can I total diagonally "the
other way" (here A5, B4, C3, D2 and E1)?

</QUESTION>

<ANSWER/>

To sum A5:E1 diagonally, use:

=SUM(N(OFFSET(E1,ROW(1:5)-1,-1*ROW(1:5)+1)))

Or (not Array Entered):
=SUMPRODUCT(N(OFFSET(E1,ROW(INDIRECT("1:5"))-1,-1*ROW(INDIRECT("1:5"))+1)))

It would be better to use ROW(INDIRECT("1:5") in all the formulas, like

=SUM(N(OFFSET(E1,ROW(INDIRECT("1:5"))-1,-1*ROW(INDIRECT("1:5"))+1)))

as inserting a row above row 5 will change the reference.

To sum across sheets (Sheet1!A1  Sheet2!B2  Sheet3!C3..etc) use:

=SUM(N(INDIRECT("Sheet"&ROW(1:5)&"!"&ADDRESS(ROW(1:5),ROW(1:5)))))

Or (not Array Entered):

=SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:5)&"!"&ADDRESS(ROW(1:5),ROW(1:5)))))

</ANSWER>

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

VBA CODE EXAMPLES 

by Tom Ogilvy and Dana DeLouis

<QUESTION/>

I'd like to expand the Custom Autofilter to 3 or more entries.

</QUESTION>

<ANSWER/>

This procedure assumes you want to display cells that have a,b, and c in 
the Cell in column A. If you are looking for multiple "Or" conditions, then 
use Union instead of Intersect.


Sub MultCustomAutoFilter()

  Dim rng1 As Range
  Dim rng2 As Range
  Dim rngAll3 As Range

  Range("A1").AutoFilter

  With [_FilterDatabase].Offset(1, 0)
    Range("A1").AutoFilter Field:=1, Criteria1:="*a*", Operator:=xlAnd, _
    Criteria2:="*b*"
    Set rng1 = .SpecialCells(xlVisible)
    Range("A1").AutoFilter Field:=1, Criteria1:="*c*"
    Set rng2 = .SpecialCells(xlVisible)
    ActiveSheet.AutoFilterMode = False
    Set rngAll3 = Application.Intersect(rng1, rng2)
    .EntireRow.Hidden = True
    rngAll3.EntireRow.Hidden = False
  End With

End Sub

This procedure works for Excel 2000.  For prior version change the range
object in the With statement to:

  ActiveSheet.AutoFilter.Range.Offset(1, 0)

</ANSWER>


by Bill Manville

<QUESTION/>

Is there a way to reliably code the show detail and hide detail commands 
off of the "Data" menu into a VB macro?

</QUESTION>

<ANSWER/>

This feature is not well supported by VBA. Thus, to hide the detail for the 
block within which the cursor sits, use:

ExecuteExcel4Macro "SHOW.DETAIL(1," & ActiveCell.Row & ",FALSE)"

</ANSWER>


by Stephen Bullen

<QUESTION/>

How can I change the name of the vbcomponent based on the name of the 
related worksheet?

</QUESTION>

<ANSWER/>

 The CodeName of a sheet can be changed with:

 Sub RenameCodeName(oWks As Sheet, sNewName As String)

 oWks.Parent.VBProject.vbComponents(oWks.CodeName) _
     .Properties("_CodeName") = sNewName

 End Sub

</ANSWER>

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

POWER PROGRAMMING TECHNIQUE

by Bill Manville

<QUESTION/>

I have to check the contents of a large spreadsheet against a second more 
up to date spreadsheet in another workbook.  The structure of the 2 workbooks
is the same. How can I identify which cells differ so I can investigate 
those individually.

</QUESTION>

<ANSWER/>

This procedure creates a new workbook which lists the comparison results for
each worksheet in the two workbooks of interest. Each of the two workbooks 
should be open prior to running this procedure. Replace the dummy names in the 
the DoCompare sub with appropriate filenames.

Sub DoCompare()
  Dim WS As Worksheet
  Workbooks.Add
  For Each WS In WorkBooks("SomeBook.xls").Worksheets
    CompareSheets WS, Workbooks("SomeOther.xls").Worksheets(WS.Name)
  Next
End Sub

Sub CompareSheets(WS1 As Worksheet, WS2 As Worksheet)
  Dim iRow As Integer, iCol As Integer
  Dim R1 As Range, R2 As Range
  Worksheets.Add.Name = WS1.Name ' new book for the results
  Range("A1:D1").Value = Array("Address", "Difference", WS1.Parent.Name, WS2.Parent.Name)
  Range("A2").Select
  For iRow = 1 To Application.Max(WS1.Range("A1").SpecialCells(xlLastCell).Row, _
                      WS2.Range("A1").SpecialCells(xlLastCell).Row)
    For iCol = 1 To Application.Max(WS1.Range("A1").SpecialCells(xlLastCell).Column, _
                      WS2.Range("A1").SpecialCells(xlLastCell).Column)
      
      Set R1 = WS1.Cells(iRow, iCol)
      Set R2 = WS2.Cells(iRow, iCol)
      
      ' compare the types to avoid getting VBA type mismatch errors.
      If TypeName(R1.Value) <> TypeName(R2.Value) Then
        NoteError R1.Address, "Type", R1.Value, R2.Value
      ElseIf R1.Value <> R2.Value Then
        If TypeName(R1.Value) = "Double" Then
          If Abs(R1.Value - R2.Value) > R1.Value * 10 ^ (-12) Then
            NoteError R1.Address, "Double", R1.Value, R2.Value
          End If
        Else
            NoteError R1.Address, "Value", R1.Value, R2.Value
        End If
      End If
      
      ' record formulae without leading "=" to avoid them being evaluated
      If R1.HasFormula Then
        If R2.HasFormula Then
          If R1.Formula <> R2.Formula Then
            NoteError R1.Address, "Formula", Mid(R1.Formula, 2), Mid(R2.Formula, 2)
          End If
        Else
          NoteError R1.Address, "Formula", Mid(R1.Formula, 2), "**no formula**"
        End If
      Else
        If R2.HasFormula Then
          NoteError R1.Address, "Formula", "**no formula**", Mid(R2.Formula, 2)
        End If
      End If
      If R1.NumberFormat <> R2.NumberFormat Then
        NoteError R1.Address, "NumberFormat", R1.NumberFormat, R2.NumberFormat
      End If
    Next iCol
  Next iRow
  With ActiveSheet.UsedRange.Columns
    .AutoFit
    .HorizontalAlignment = xlLeft
  End With
End Sub

Sub NoteError(Address As String, What As String, V1, V2)
  ActiveCell.Resize(1, 4).Value = Array(Address, What, V1, V2)
  ActiveCell.Offset(1, 0).Select
  If ActiveCell.Row = Rows.Count Then
    MsgBox "Too many differences", vbExclamation
    End
  End If
End Sub

</ANSWER>

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

EXCEL TIPS

by John Green

<QUESTION/>

I need a simple macro to take the cell text in a selected cell(s) and add 
characters such as "." after the text until the cell is filled to its width.
You get something like:
 
Text_here.....
and................
here..............

</QUESTION>

<ANSWER/>

All you need to do is apply a custom format (Format|Cells - Number) to the 
cell such as:

	@*.
  
@ is a place marker for the text and the character after the asterisk is 
repeated to fill the cell. "*.@" fills to the left. 

If you want to do this in code:

	Range("A1:A10").NumberFormat = "@*."


</ANSWER>

Note: If this is done with cells containing numbers, they cannot be operated 
on as numbers since they are formatted as text. As a workaround to this 
problem, use:

=SUM(VALUE(range))  ' array-entered

instead of

=SUM(range)


by George Simms

<QUESTION/>

The problem I have is that in order to add a value to an existing value 
field I need to insert an "=" at the beginning of the cell before it displays
the solution. If I simply type the "+" or "-" after the value Excel displays
the formula (obviously interpreting it as text). Can I perform this task
without inserting the "=" each time?

</QUESTION>

<ANSWER/>

There is a way to do what you want, if you use it to only edit your data.

From the menu > Tools > Options >Transition tab> check the "Transition
formula entry" box.

It is recommend that once you have edited the data, go back and uncheck
the box.  If left checked this can produce some odd results, entering
dates etc.....

</ANSWER>


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

Issue No.18 OF EEE (PUBLISHED 01Apr2000)
Next issue scheduled for 01May2000.
BY David Hager
dchager@compuserve.com

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