count numbers of entries according to parts of string

If you would like to count the entries in an area containing text-values according to parts of these entries you can use the following schema:

{SUM(--(LEFT($A$1:$A$15,3)="ABC")}

explanation:

• $A$1:$A$15 - area to be examined - contains the strings
• LEFT($A$1:$A$15,3)="ABC" - checks the first three letters of each cell matching "ABC"- delivers an array of boolean values (TRUE, FALSE, ...)
  
• --( ) - converts boolean values in binary values TRUE=1; FALSE=0
• {} - Array-Function (needs to be entered using CRL-SHIFT-ENTER)

Have phun playing with it...

Dynamic Ranges in Pivot Tables

1st way is to change the reference range the pivot-table is assigned to - use the Wizard - go back and enter the new range of data - press finish - new data inserted

2nd way - more sophisticated, since automatically - use a named range:

Use a Dynamic Data Source You can use a dynamic formula to define the source range for a Pivot Table. As new items are added to the table, the named range will automatically expand. 1. Name the Range Choose Insert>Name>Define Type a name for the range, e.g. Database In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in a column that doesn't contain any blank cells. , e.g.: =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7) In this example, the list is on a sheet named 'Data', starting in cell A1. The arguments used in this Offset function are: Reference cell: Data!$A$1 Rows to offset: 0 Columns to offset: 0 Number of Rows: COUNTA(Data!$A:$A) Number of Columns: 7 Note: for a dynamic number of columns, replace the 7 with: COUNTA(Data!$1:$1) Click OK

2. Base the Pivot Table on the Named Range Select a cell in the database Choose Data>PivotTable and PivotChart Report Select 'Microsoft Excel List or Database', click Next. For the range, type your range name, e.g. Database Click Next Click the Layout button Drag field buttons to the row, column and data areas Click OK, click Finish

How to protect/unprotect sheets in VBA...

If you have protected a single sheet in Excel to prevent from user changes, you may have to edit information on this sheet while processing the VBA script.

Just add
Activesheet.Unprotect("password") to unprotect the sheet
and
Activesheet.Protect("password") to protect the sheet again.

=> Replace "password" by the password of this sheet

Hint: protecting the VBA-project with a separate passwords makes you (somehow) sure against users who try to access your code ;-)