Main Topics
Browse All Topics
Loading Advertisement...
View Profile
View Profile
Personal Description
Hello from Adelaide, Australia
A brief list of my EE articles with descriptions below
Finance Related
1. An overview of using NPV and IRR in Excel for evaluating investment decisions http://www.experts-exchang
Addins
1. Mappit! - a free Excel model auditing addin http://www.experts-exchang
a. Produces a live map (goto button) with a detailed map of each sheet in the file analyst
b. A 2*2 summary table showing the relationships between the worksheets
c. An overall summary of each worksheet (unique formula, used range, hidden cells, cell comments)
2. Eliminating duplicate data with Duplicate Master V2 Article http://www.experts-exchang
a. Allows the highlighting/deletion/sele
b. Includes string manipulation options for matching
i. Case Insensitive
ii. TRIM an/or CLEAN functions
iii. Ignore all whitespace (including Char 160)
iv. Regular Expression matching
c. Allows for the reporting of unique cells, rows or particular columns columns over either a range/sheet/selected sheet or entire workbook
i. Extract records that occur at least once
ii. Extract records that occur only once
Efficiency
1. A fast method for determining the unlocked cell range http://www.experts-exchang
a. Uses deliberate errors with SpecialCells to quickly map the unlocked cell range
2. Using Variant Arrays in Excel VBA for Large Scale Data Manipulation http://www.experts-exchang
a. Shows how to use variant arrays with ranges to quickly manipulate data
b. Caters for ranges that may contain single cells (which cannot be used in variant arrays)
c. This sample code removes all leading zero’s from each cell in a user selection
3. Creating and Writing to a CSV File Using Excel VBA Article http://www.experts-exchang
a. Shows how to create a CSV File via VBA, a very efficient method of creating reports
b. This sample code transposes rows and columns within a selected range
Common Requests
1. Collating worksheets from one or more workbooks into a summary file Article http://www.experts-exchang
a. This code provides three options:
i. Collate all sheets from all Excel workbooks in a single folder into a single summary worksheet
ii. Collate all sheets from all Excel workbooks in a single folder into a single summary workbook
iii. Collate all sheets from a single Excel workbook into a single summary worksheet
2. Using Find and FindNext to efficiently delete any rows that contain specific text Article http://www.experts-exchang
a. An efficient method to delete rows
3. Quickly creating a summary differential sheet or linked output sheet - ignoring any blank cells Article http://www.experts-exchang
a. An method to quickly link one sheet to another file with formula feeds
Summary Lists
1. Produce an Excel list of the attributes of all MP3 files that sit in or below the "My Music" folder Article http://www.experts-exchang
a. Catalogue your music collection into Excel by Author, Album, Genre etc
b. Also shows a recursive Dir method that can be used in place of FileSearch (deprecated from xl07)
c. Works on XP/Vista/Windows 7
2. Excel VBA to create a Table of Contents (TOC) summary sheet Article http://www.experts-exchang
a. Creates a table of contents for all sheets
b. includes the programmatic addition of code as a workaround for selecting chart sheets
3. Excel code to find and report on links in the selected ActiveWorkbook (formulas, range names, pivotTables & charts) Article 972
Techniques
1. Trapping a change to a filtered list with VBA http://www.experts-exchang
a. A workaround that effectively creates a “Change_Filter” event
2. Adding a "Subtract Range" method alongside Union & Intersect Article http://www.experts-exchang
a. Uses variant arrays in conjunction with SpecialCells to provide a Subtract Range function
3. Using a recursive NavigateArrows Method to analyse all local and off sheet cell dependencies Article http://www.experts-exchang
a. Excel is quirky when it comes to handling off-sheet precedents and dependents. This code provides a sample for analysing whether a particular number is used anywhere in the precedents of any cells in a certain
summary sheet
4 Using XLM with Range Names and Conditional Formatting to automatically format spreadsheets according to cell content Article http://www.experts-exchang
a. Options to automatically highlight formulae, links to external workbooks, links to other worksheets
b. As a technique to highlight inconsistent cells
A brief list of my EE articles with descriptions below
Finance Related
1. An overview of using NPV and IRR in Excel for evaluating investment decisions http://www.experts-exchang
Addins
1. Mappit! - a free Excel model auditing addin http://www.experts-exchang
a. Produces a live map (goto button) with a detailed map of each sheet in the file analyst
b. A 2*2 summary table showing the relationships between the worksheets
c. An overall summary of each worksheet (unique formula, used range, hidden cells, cell comments)
2. Eliminating duplicate data with Duplicate Master V2 Article http://www.experts-exchang
a. Allows the highlighting/deletion/sele
b. Includes string manipulation options for matching
i. Case Insensitive
ii. TRIM an/or CLEAN functions
iii. Ignore all whitespace (including Char 160)
iv. Regular Expression matching
c. Allows for the reporting of unique cells, rows or particular columns columns over either a range/sheet/selected sheet or entire workbook
i. Extract records that occur at least once
ii. Extract records that occur only once
Efficiency
1. A fast method for determining the unlocked cell range http://www.experts-exchang
a. Uses deliberate errors with SpecialCells to quickly map the unlocked cell range
2. Using Variant Arrays in Excel VBA for Large Scale Data Manipulation http://www.experts-exchang
a. Shows how to use variant arrays with ranges to quickly manipulate data
b. Caters for ranges that may contain single cells (which cannot be used in variant arrays)
c. This sample code removes all leading zero’s from each cell in a user selection
3. Creating and Writing to a CSV File Using Excel VBA Article http://www.experts-exchang
a. Shows how to create a CSV File via VBA, a very efficient method of creating reports
b. This sample code transposes rows and columns within a selected range
Common Requests
1. Collating worksheets from one or more workbooks into a summary file Article http://www.experts-exchang
a. This code provides three options:
i. Collate all sheets from all Excel workbooks in a single folder into a single summary worksheet
ii. Collate all sheets from all Excel workbooks in a single folder into a single summary workbook
iii. Collate all sheets from a single Excel workbook into a single summary worksheet
2. Using Find and FindNext to efficiently delete any rows that contain specific text Article http://www.experts-exchang
a. An efficient method to delete rows
3. Quickly creating a summary differential sheet or linked output sheet - ignoring any blank cells Article http://www.experts-exchang
a. An method to quickly link one sheet to another file with formula feeds
Summary Lists
1. Produce an Excel list of the attributes of all MP3 files that sit in or below the "My Music" folder Article http://www.experts-exchang
a. Catalogue your music collection into Excel by Author, Album, Genre etc
b. Also shows a recursive Dir method that can be used in place of FileSearch (deprecated from xl07)
c. Works on XP/Vista/Windows 7
2. Excel VBA to create a Table of Contents (TOC) summary sheet Article http://www.experts-exchang
a. Creates a table of contents for all sheets
b. includes the programmatic addition of code as a workaround for selecting chart sheets
3. Excel code to find and report on links in the selected ActiveWorkbook (formulas, range names, pivotTables & charts) Article 972
Techniques
1. Trapping a change to a filtered list with VBA http://www.experts-exchang
a. A workaround that effectively creates a “Change_Filter” event
2. Adding a "Subtract Range" method alongside Union & Intersect Article http://www.experts-exchang
a. Uses variant arrays in conjunction with SpecialCells to provide a Subtract Range function
3. Using a recursive NavigateArrows Method to analyse all local and off sheet cell dependencies Article http://www.experts-exchang
a. Excel is quirky when it comes to handling off-sheet precedents and dependents. This code provides a sample for analysing whether a particular number is used anywhere in the precedents of any cells in a certain
summary sheet
4 Using XLM with Range Names and Conditional Formatting to automatically format spreadsheets according to cell content Article http://www.experts-exchang
a. Options to automatically highlight formulae, links to external workbooks, links to other worksheets
b. As a technique to highlight inconsistent cells
Loading Advertisement...
Top Experts
-
hanccocka
777,299
Sage
10,230 points yesterday
ProfileRank: Genius -
demazter
729,187
Sage
7,200 points yesterday
ProfileRank: Genius -
CodeCruiser
474,531
Wizard
10 points yesterday
ProfileRank: Genius -
alanhardisty
386,199
Wizard
1,040 points yesterday
ProfileRank: Genius -
capricorn1
370,028
Wizard
5,500 points yesterday
ProfileRank: Savant -
arnold
366,274
Wizard
7,440 points yesterday
ProfileRank: Genius -
Ray_Paseur
359,639
Wizard
4,080 points yesterday
ProfileRank: Savant -
kaufmed
356,108
Wizard
8,520 points yesterday
ProfileRank: Genius -
for_yan
347,242
Wizard
5,800 points yesterday
ProfileRank: Genius -
DaveBaldwin
346,142
Wizard
6,264 points yesterday
ProfileRank: Genius -
dlmille
297,769
Guru
6,840 points yesterday
ProfileRank: Genius -
COBOLdinosaur
296,865
Guru
5,664 points yesterday
ProfileRank: Genius -
woolmilkporc
276,703
Guru
0 points yesterday
ProfileRank: Genius -
mbizup
265,738
Guru
4,000 points yesterday
ProfileRank: Genius -
slightwv
256,524
Guru
4,000 points yesterday
ProfileRank: Genius -
matthewspatrick
241,988
Guru
4,040 points yesterday
ProfileRank: Savant -
jason1178
225,040
Guru
1,770 points yesterday
ProfileRank: Genius -
thinkpads_user
217,724
Guru
7,100 points yesterday
ProfileRank: Genius -
apache09
204,212
Guru
0 points yesterday
ProfileRank: Genius -
sdstuber
203,502
Guru
60 points yesterday
ProfileRank: Genius -
dariusg
201,588
Guru
10 points yesterday
ProfileRank: Genius -
nap0leon
201,076
Guru
1,000 points yesterday
ProfileRank: Wizard -
mwvisa1
195,365
Guru
1,140 points yesterday
ProfileRank: Genius -
Run5k
193,622
Guru
5,000 points yesterday
ProfileRank: Sage -
LSMConsulting
187,283
Guru
2,000 points yesterday
ProfileRank: Savant