Excel

Microsoft Excel has become the de-facto standard for spreadsheets.

Named lists

Named lists are extremely useful when working with a range of cells. a constant value or a formula. The Named list can be used in formulas, to replace values or cell references.
There are several possibilities :

• Name a range - Excel Name Box
• Dynamic Named Ranges

Excel Name Box

To use the Excel Name Box:

1. Select the cell(s) to be named
2. Click on the Excel Name Box, to the left of the formula bar
3. Press the enter key

Dynamic Named Ranges

Another way to create Excel names is to use a dynamic formula to define an Excel named range. As new items are added, the range will automatically expand.
Note: Dynamic named ranges will not appear in the Name Box dropdown list. However, you can type the Excel names in the Name Box, to select the range on the worksheet.

1. Choose Insert > Name > Define
2. Type a name for the range i.e. NameList
3. In the Refers to: textfield, enter an Offset formula that defines the range size, based on the number of items in the column.
   i.e. : =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$a:$a),1) meaning:
   1. Reference cell: Sheet1!$A$1
   2. Rows to offset: 0
   3. Columns to offset: 0
   4. Elements of the row: COUNTA(Sheet1$a:$a)
   5. Number of Columns: 1
4. Click Ok.

Countif for Multiple Criteria

The Excel function for Multiple criteria does not exist in Excel 2003. Use SUMPRODUCT" instead. This is how it works:

  =SUMPRODUCT ( Criteria_range_1 [assignment] Criteria_1) * (Criteria_range_2 [assignment] Criteria_2) * ... )
  where: 
  Citeria_range  -  the group of cells the function is to search.
  Criteria           -  determines whether the cell is to be counted or not.

Example Description:

• The rows will only be counted if they meet the following criteria:
• Column E: if the number is less than or equal to 2;
• Column F: if the number is equal to 4;
• Column G: if the number is greater than or equal to 5.

Example Excel:

1. Enter the following data into cells E1 to E6: 1, 2, 1, 2, 2, 8.
2. Enter the following data into cells F1 to F6: 4, 4, 6, 4, 4, 1.
3. Enter the following data into cells G1 to G6: 5, 1, 5, 3, 8, 7.
4. Type the heading SUMPRODUCT: into cell H1.
5. Click on cell I1 - the location where the function results will be displayed.
6. Type the following into cell I1:
   = sumproduct ((E1:E6<=2)*(F1:F6=4)*(G1:G6>=5)) and press the ENTER key on the keyboard.
7. The answer 2 should appear in cell I1 since there are only two rows (rows 1 and 5) that meet all three of the criteria listed above.
8. The complete function = SUMPRODUCT ((E1:E6<=2)*(F1:F6=4)*(G1:G6>=5)) appears in the formula bar above the worksheet when you click on cell I1.

Events VBA

Event programming is a very powerful tool that you can use within your VBA code:

• to monitor user actions,
• take appropriate action when a user does something,
• monitor the state of the application as it changes,
• for forcing excel to re-calculate when VBA User Defined Methods are used.

If you are programming with your own custom classes, you can extend the functionality of these classes by defining and raising your own custom events, broadcasting the event message to any object that is listening for events from your class.

Events and Event Procedures were introduced in Excel97. Earlier versions of Excel do not support events. Events and event procedures are the same for versions 97 through 2007. No significant changes have been made to the event model since its introduction in Excel97. A few new events have been added, but the overall structure of the event system is unchanged.

• C Pearson, Events in Excel VBA

Pivot Table

A Pivot table is not an invention of Microsoft. But in Excel MS have implemented the pivot table with much noise.

• ZK Pivot Table Concept, excellent explanation for the usage of Pivot Tables.

Office

• http://support.microsoft.com/ofd Microsoft Office Development
• http://msdn2.microsoft.com/en-us/office/default.aspx Microsoft Office MSDN

Force global recalculation

Q	Sometimes my formulas do not get fully calculated. This often happens when using custom functions created with VBA.
A	Microsoft has acknowledged some problems with the Excel calculation engine in some version of Excel. In order to be assured that all of your formulas have been calculated, press Ctrl-Alt-F9 to force a complete recalculation. This key combination will also update formulas that use custom VBA functions.

See also:

• Microsoft Office Library, Microsoft Excel Recalculation