Files

Hopefully these files are a help

to you solving your own project hurdles.

If you have a need, email me and I'll do my best to help.
     -- Jerry     (JBeau@madrocketscientist.com)
PayPal - The safer, easier way to pay online!
If I've helped in a good way,
buy me lunch...
or dinner!

Ĉ 3D-VLOOKUP&HYPERLINK.xls
View Download
An array formula that does a 3D VLOOKUP() across multiple sheets and returns the match from the first sheet with a positive match. A 3D HYPERLINK formula can then jump directly to the found item on the correct sheet.  23k v. 1 Nov 6, 2010, 7:33 PM Jerry Beaucaire
Ĉ 3D-VLOOKUP.xls
View Download
An array formula that does a VLOOKUP() across multiple sheets and returns the match from the first sheet with a positive match.  21k v. 1 Mar 27, 2010, 11:53 AM Jerry Beaucaire
Ĉ AccumulateColumns.xls
View Download
Consolidate a workbook with many sheets into a single sheet placing the columns of data side by side in the consolidation sheet.  36k v. 1 Jan 28, 2013, 12:45 AM Jerry Beaucaire
Ĉ Auto-AutofilterMacro.xls
View Download
Button macro to automatically increment through a column with an Autofilter one unique value at a time.   43k v. 2 May 3, 2010, 11:09 PM Jerry Beaucaire
Ĉ AutoCompleteDataValidation(SortOf)2.xls
View Download
Autocomplete in a data validation list...SINGLE list of alphabetic names is parsed by the first letter.   41k v. 2 Nov 16, 2010, 1:58 PM Jerry Beaucaire
Ĉ AutoCompleteDataValidation(SortOf)3.xls
View Download
Autocomplete in a data validation list... Multiple lists of categories... operating dependently in the same cell   22k v. 2 Mar 22, 2010, 6:05 PM Jerry Beaucaire
Ĉ AutoCompleteDataValidation(SortOf).xls
View Download
A workaround that gets the data validation in one cell to offer filtered options, all in the same cell. Not exactly "autocomplete", but a very usable alternative.    18k v. 4 Mar 15, 2010, 3:54 PM Jerry Beaucaire
Ĉ CalendarPOPUP.xls
View Download
Hidden sheet "events" lists holidays. When workbook is opened, a macro will read the events and give a popup message of today's holidays. Unhide EVENTS sheet to edit the list.  47k v. 2 Dec 31, 2009, 5:26 PM Jerry Beaucaire
Ĉ ColumnsToRows-ConsolidationMacros.xls
View Download
Techniques for merging column data into row format. Part 1 - all data in a single column. Part 2 - duplicate values in column A used to create unique list with all column B values retained (either separate cells or merged into a single cell). Part 3 - duplicates in column A, many columns of data to merge into one row per unique value. Part 4 - merge qty column(s) for same items in duplicated database. Part 5 - Multi-column data formatted down to two-columns, headers in col 1, data in col 2.Part 6 - Two-column categorized data is parsed into a row format with random columns.   78k v. 12 Jun 15, 2013, 1:30 AM Jerry Beaucaire
Ĉ ColumnsToSheets.xls
View Download
Sample file - columns of data are parsed to individual sheets, user can set the starting column to parse and how many columns to parse to each sheet.   29k v. 2 Aug 7, 2011, 2:28 AM Jerry Beaucaire
Ĉ ColumnToColumns.xls
View Download
Data groups in a single column separated by blank spaces are parsed into individual columns using the Areas method.  30k v. 2 May 19, 2012, 11:48 AM Jerry Beaucaire
Ĉ CommissionStructures(ComplexvsSimpleTiers).xls
View Download
Shows the difference between SIMPLE discounts (discount applies to all items as QTY increases) and COMPLEX TIERED discount (discount applied to each additional group of items purchased, not to all items purchased).   27k v. 3 Aug 24, 2011, 3:21 AM Jerry Beaucaire
Ĉ CopyXRows.xls
View Download
How to copy 10 rows of a filtered data set.   32k v. 2 Nov 29, 2010, 12:24 PM Jerry Beaucaire
Ĉ CreateHyperlinkSheetList.xls
View Download
Makes a hyperlinked listing of all worksheets in the workbook. Adds key data from sheets to listing, adds a hyperlink on all sheets back to this new LIST sheet for fast-linkbased-navigation.  32k v. 7 Aug 4, 2012, 10:49 AM Jerry Beaucaire
Ĉ CreateSheets.xls
View Download
Macro to create sorted sheets in a workbook with hyperlinks from an index sheet.   28k v. 2 Nov 9, 2010, 11:44 AM Jerry Beaucaire
Ĉ CSVsToWBs.xls
View Download
Using the parameters on a setup sheet, the files of a specific type are converted to Excel files.   34k v. 3 Jan 2, 2013, 8:43 AM Jerry Beaucaire
Ĉ DependentDYNAMICLists.xls
View Download
Shows a technique to get around the fact that Dynamic Named Ranges and INDIRECT() don't work together when creating cascading drop downs. This approach allows the secondary lists to be dynamically created.  30k v. 3 Nov 30, 2012, 9:25 AM Jerry Beaucaire
Ĉ DependentLists2.xls
View Download
Dependent DropDown...2 levels. This also shows how to pull matching data from from the Database sheet once a final choice has been made.   25k v. 1 Nov 7, 2009, 9:29 AM Jerry Beaucaire
Ĉ DependentLists3.xls
View Download
Shows 3 levels of dependent dropdown lists. Also shows how to make accidental illegal matches impossible. If macros are not an option, a CF solution for error handling is shown.  28k v. 2 Mar 11, 2010, 8:36 AM Jerry Beaucaire
Ĉ DependentLists-SelfSorting.xls
View Download
Dependent Drop down lists that sort themselves  23k v. 1 Nov 7, 2009, 9:30 AM Jerry Beaucaire
Ĉ DiminishingDropDownList-NonArray.xls
View Download
Data validation list that only allows each item to be used once in all the cells using the same list. Once an item is selected, it disappears from the list, returning if the item is deleted from the prior chosen cell.  16k v. 1 Nov 11, 2009, 11:21 AM Jerry Beaucaire
Ĉ DomainExtraction-UDF.xls
View Download
Extract the domain string from a hostname string.  42k v. 1 Sep 2, 2010, 9:05 AM Jerry Beaucaire
Ĉ DuplicateRows.xls
View Download
Using the last column of data, each unique row is duplicated that number of times. Dynamic, works with any number of rows/columns.  31k v. 2 Jul 10, 2012, 3:24 PM Jerry Beaucaire
Ĉ DV-CorrectPriorChoices.xls
View Download
When corrections are made to the "source lists" used for data validation drop downs, this technique will cause the cells that already used that "list" to update as well.  31k v. 1 Aug 14, 2011, 11:45 PM Jerry Beaucaire
Ĉ DV-MiniDropdownsfromLargeList2.xls
View Download
From a single 2-column table a dependent drop down displays only the range2 values that match the range1 category.  29k v. 1 Nov 16, 2010, 4:16 PM Jerry Beaucaire
Ĉ DV-ReplaceChoices.xls
View Download
Data validation choices are replaced on the fly by related information.  24k v. 1 Aug 19, 2011, 2:50 PM Jerry Beaucaire
Ĉ EventTimes.xls
View Download
Use conditional formatting to highlight the "Current Event" from a day of activities  25k v. 1 Mar 25, 2010, 10:18 AM Jerry Beaucaire
Ĉ ExcelCalendar.xls
View Download
A calendar in Excel that formats a month correctly for any given month.  22k v. 3 Nov 11, 2009, 11:39 AM Jerry Beaucaire
Ĉ FillOutTemplate-DuplicationMacro.xls
View Download
Data sheet is fed into a template, template is copied and saved as a form...either as a worksheet or new workbook.   35k v. 1 Apr 25, 2010, 12:53 AM Jerry Beaucaire
Ĉ Gantt Chart.xls
View Download
Gantt charts depict a range of days with a colored bar on a horizontal calendar. This file generates Gantt bars bases on start/end dates for each person entered.  17k v. 2 Feb 23, 2012, 12:45 PM Jerry Beaucaire
Ĉ HyperlinkedDirectoryListing.xls
View Download
Macro to create a file listing of any folder with optional filters and hyperlinks.  27k v. 1 Nov 19, 2010, 12:33 PM Jerry Beaucaire
ċ
ImportConsolidationMacro.zip
View Download
Macro to import data in randomly ordered columns on multiple sheets into a new consolidated workbook importing only the columns you setup in your target workbook.   14k v. 2 Jun 23, 2010, 8:16 AM Jerry Beaucaire
Ĉ Index-Match-Sample.xls
View Download
Using a selection box, choose an ID and all the information for that ID from another database set appears in a chart. An example for drawing across ONE row of data, and an example for drawing across MULTIPLE rows of matching data.   31k v. 2 Jan 29, 2010, 6:17 PM Jerry Beaucaire
Ĉ IndexMatch-Simple.xls
View Download
Ways to draw data from a "table" of information. This sheet includes examples of 1-dimensional data and 2-dimensional "tables". Techniques include LOOKUP(), VLOOKUP() and the preferred INDEX/MATCH.   40k v. 6 Jan 29, 2010, 6:18 PM Jerry Beaucaire
Ĉ InsertRowsMacro.xls
View Download
User File  28k v. 1 Nov 23, 2010, 2:13 PM Jerry Beaucaire
Ĉ Invoice.xlt
View Download
This template demonstrates a self-contained autonumbering system for all invoices generated from this template. Update the enclosed macro to indicate where the invoice is stored on your system for it to operate correctly.  29k v. 1 Sep 6, 2011, 5:13 PM Jerry Beaucaire
Ĉ LapCounter.xls
View Download
A macro enabled worksheet showing how to start a race with multiple players and then log "lap" time for each runner.START ALL button will start the timer for all runners.The LAP button on each row will enter a time for one lap and restart the timer for the next lap. --Sheet2 shows a simple start/stop/time lapsed timer. --Sheet3 shows a ROWING stroke analyzer.  51k v. 8 Feb 8, 2010, 9:52 AM Jerry Beaucaire
Ĉ LineEmUp.xls
View Download
Various examples of aligning groups of data by match column values.    76k v. 3 Jul 25, 2011, 10:10 PM Jerry Beaucaire
Ĉ Lotto-Tracker.xls
View Download
Simple sheet for matching lotto picks to a list of purchased tickets.  20k v. 1 Jul 27, 2010, 4:11 AM Jerry Beaucaire
Ĉ MakeDirectories.xls
View Download
List needed directories in column A and macro creates all folders needed to complete all paths listed.   34k v. 2 Oct 19, 2011, 10:49 AM Jerry Beaucaire
Ĉ MakeNameRanges.xls
View Download
Using the lists on one sheet, create/update a series of named ranges based on those groups of values.  25k v. 1 Nov 14, 2010, 9:14 PM Jerry Beaucaire
ċ
MassReplaceExample.xlsm
Download
Uses a two-column reference table to do multiple SEARCH/REPLACE functions on a column of data.  19k v. 1 Feb 18, 2012, 6:05 AM Jerry Beaucaire
Ĉ MergeRows.xls
View Download
Reduce a table of duplicate IDs and data values to a single row per ID, data values are merged into a single cell with delimiter  22k v. 1 Feb 6, 2012, 7:07 AM Jerry Beaucaire
Ĉ MultiFindCopy.xls
View Download
Macro prompts for multiple search strings, all rows with any matching values anywhere copy whole row to second sheet. Copied rows maintain their original order. Added version to copy only rows that have ALL the searched strings.   57k v. 4 Jul 19, 2012, 9:48 AM Jerry Beaucaire
Ĉ MultiFindCopy.xlsm
Download
Macro prompts for multiple search strings, all rows with any matching values anywhere copy whole row to second sheet. Copied rows maintain their original order. Added version to copy only rows that have ALL the searched strings.  24k v. 5 Jul 19, 2012, 9:49 AM Jerry Beaucaire
Ĉ MultiTimers2.xls
View Download
Start multiple countdown / countup timers and turn them off individually while others continue to run.    41k v. 3 Oct 12, 2010, 9:37 AM Jerry Beaucaire
Ĉ MultiTimers3.xls
View Download
Start/Stop 1-4 separate "down time" timers.   64k v. 3 Sep 28, 2012, 4:26 PM Jerry Beaucaire
Ĉ ParseWords.xls
View Download
UDF called WORD() allows user to pull any word or words from a longer sentence-style text string.  35k v. 1 Oct 11, 2010, 3:05 PM Jerry Beaucaire
Ĉ PasswordHiddenSheets2-MoreSheetsPerPassword.xls
View Download
Like the other PasswordHiddenFiles.xls sample, but this one lets each password reveal multiple hidden sheets.   42k v. 2 Sep 5, 2011, 8:09 AM Jerry Beaucaire
Ĉ PasswordHiddenSheets.xls
View Download
From a front sheet, a button prompts for a password, different passwords open different hidden sheets. Sheets hide again automatically, and passwords can be changed.    38k v. 3 Sep 2, 2011, 12:59 PM Jerry Beaucaire
Ĉ ProtectUnprotectAllFilesInAFolder.xls
View Download
Protect or Unprotect all the files in a folder. One macro does either, prompts for folder selection, type of protection and password.    25k v. 3 Mar 4, 2010, 6:05 PM Jerry Beaucaire
Ĉ RandomizeList.xls
View Download
Change the order of a list randomly.  30k v. 1 Jul 24, 2010, 3:13 AM Jerry Beaucaire
Ĉ RandomNumGenerator.xls
View Download
Technique for generating non-repeating random selection from a set list of options.     156k v. 4 Oct 23, 2010, 8:53 AM Jerry Beaucaire
ċ
RandomTaskListWithAUDIO.xlsm
Download
Randomize a list of names and tasks, then pressing REVEAL over and over to display results and play fun audio.  22k v. 1 Nov 10, 2010, 4:59 PM Jerry Beaucaire
Ĉ Rank-TieBreaking.xls
View Download
Show many methods of Ranking scores with multiple tie-breaking approaches.     44k v. 4 Feb 1, 2010, 3:01 PM Jerry Beaucaire
Ĉ RemoveString.xls
View Download
user file  17k v. 1 Feb 16, 2012, 4:44 PM Jerry Beaucaire
Ĉ RowsToColumns-ParseMacros.xls
View Download
Techniques for parsing data that is in row format into column format. Part 1a - multiple columns of data put into single column, duplicating key data for each row created.(two codes) Part 1b/1c - similar to 1a, you can indicate how many column make a group and where to start the split down. Part 2 - data in a single column that is delimited by commas, colons or Alt-Enter characters is parsed to individual cells, all other cells in the row are duplicated. Part 3 - reformat a table of data into column format.   Part 4 - reformat data-only table into 2-column format.   Part 5 - rows of random values into a single column of values      96k v. 23 Mar 21, 2014, 6:16 AM Jerry Beaucaire
Ĉ SelfExpandingDataValidationLists.xls
View Download
When you type a new option into a cell with a data validation list, the new option is permanently added to the data validation list.  41k v. 1 Oct 1, 2012, 11:11 PM Jerry Beaucaire
Ĉ SelfMergingValidationList.xls
View Download
Two separate lists merge themselves in realtime into a 3rd list that is then used as a dynamic named range validation list.  16k v. 1 Apr 25, 2010, 7:19 PM Jerry Beaucaire
Ĉ Sheet1toSheets-NoVBANoBlankRows.xls
View Download
Uses formulas only to transfer rows of data from a main sheet "database" to subsheet based on one key piece of information in each row. This same splits data out by the manager assigned to each account. Change the account, the row moves automatically. No vba.  76k v. 1 Oct 15, 2011, 6:29 PM Jerry Beaucaire
Ĉ Sheet1toSheets-ParseMacros.xls
View Download
Parse data from a master data sheet onto other sheets based on the values in a single definable column.  1) creates new sheets, 2) appends to existing sheets or replaces data.       50k v. 14 Dec 23, 2013, 9:26 AM Jerry Beaucaire
Ĉ SheetsTo1Sheet-ConsolidationMacros.xls
View Download
Copy all data from all sheets in a workbook into a single consolidation sheet with the data stacked and sorted.   An option to add the sheet name each row of data came from into column A for reference.    36k v. 7 Apr 27, 2012, 10:10 AM Jerry Beaucaire
Ĉ TestBeforeClose.xls
View Download
Verifies all data in active rows have been filled in in columns E, H and K. You can save, but you can't close unless all those fields are entered.  25k v. 1 Mar 9, 2010, 10:51 AM Jerry Beaucaire
Ĉ TieredDifferentialPricing.xls
View Download
Shows a SUMPRODUCT() trick for pricing that gives a discount as each tier is attained, NOT retroactive. Each tier charges the new rate for that level while maintaining the higher prices for the earlier amounts.  15k v. 2 Mar 24, 2010, 7:04 AM Jerry Beaucaire
Ĉ Timer.xls
View Download
A simple countdown/countup timer with START/STOP buttons.  32k v. 1 May 24, 2010, 2:02 AM Jerry Beaucaire
Ĉ Toggle-HideSheet.xls
View Download
Based on the action in a single cell, a separate sheet hides and unhides in a way that only VBA can unhide it.  21k v. 1 Dec 16, 2010, 7:59 PM Jerry Beaucaire
Ĉ TreeStructure3.xls
View Download
Take an accountability personnel database and create a cascading tree. Goes as many levels deep as required.  39k v. 2 Oct 10, 2012, 5:28 AM Jerry Beaucaire
Ĉ TreeStructure.xls
View Download
Take an accountability database and construct a personnel-tree showing who reports to whom. Only goes 4 levels deep.  53k v. 3 Mar 6, 2010, 2:29 PM Jerry Beaucaire
Ĉ TxtFilesToCSVs-LimitedRows.xls
View Download
Convert a folder of TXT files into CSV files, allows each TXT file to be parsed to X rows per CSV created.  42k v. 1 Oct 16, 2012, 9:07 PM Jerry Beaucaire
Ĉ UserTimeStamps.xls
View Download
Any edits made to the workbook anywhere triggers an entry in the LOG updating the username and date/time stamp. Users are added automatically.  61k v. 2 Aug 8, 2012, 3:28 PM Jerry Beaucaire
 Pics
ą
BlueRocket100.jpg
View Download
  5k v. 2 May 27, 2010, 3:02 PM Jerry Beaucaire
ą
BlueRocket.jpg
View Download
  8k v. 2 May 27, 2010, 3:00 PM Jerry Beaucaire
ą
SneakyDV.jpg
View Download
  17k v. 2 Jul 11, 2012, 12:35 PM Jerry Beaucaire
 USER FILES
Ĉ AddressesFromURLs2.xls
View Download
From another website, another "get all the addresses" macro.  35k v. 2 Jun 21, 2010, 11:27 PM Jerry Beaucaire
Ĉ AddressesFromURLs.xls
View Download
From a list of URLs, import all the addresses from the sheet.    35k v. 3 Jun 9, 2010, 11:16 PM Jerry Beaucaire
Ĉ Alt for Indirect.xls
View Download
  35k v. 2 Oct 5, 2013, 9:57 AM Jerry Beaucaire
Ĉ ConcatIF-TestsummaryA.xls
View Download
USER FILE - Uses UDF ConcatIF to do string concatenation, similar to a SUMIF() formula, except for text strings. The UDF is installed in this sheet and active on Overview!B:B  40k v. 2 Mar 1, 2010, 8:07 PM Jerry Beaucaire
Ĉ DVtest.xls
View Download
http://www.mrexcel.com/forum/showthread.php?t=508701   14k v. 3 Nov 14, 2010, 1:20 PM Jerry Beaucaire
Ĉ LedgersToSheets.xls
View Download
User File - parse groups of rows to sheets  23k v. 2 Mar 18, 2011, 9:30 AM Jerry Beaucaire
Ĉ OptionWeights.xls
View Download
USER FILE  19k v. 2 Jun 2, 2011, 3:51 AM Jerry Beaucaire
Ĉ PanelTracker.xls
View Download
User file... worksheet_change event searches multiple pages in one sheet and creates listing on second sheet. Triggered by specific cell. (Expires in 30 days)  141k v. 2 Mar 13, 2010, 8:42 AM Jerry Beaucaire
Ĉ parseblocks.xls
View Download
Filter data in blocks from one sheet to many sheets.  30k v. 2 Apr 28, 2010, 11:24 PM Jerry Beaucaire
Ĉ PrintLabels.xlsm
Download
Fill out an INPUT sheet and an appropriate number of labels from that content is printed from a FORM sheet.  63k v. 2 Feb 12, 2013, 12:17 AM Jerry Beaucaire
ċ
SAHIL-Sequence.zip
View Download
Demonstration file for Excel query at http://www.allexperts.com/expertx.cgi?m=11&expID=120372&qID=4993390  992k v. 2 May 19, 2012, 11:49 AM Jerry Beaucaire
Ĉ TestVACTimesheet.xls
View Download
Customer sample file   16k v. 2 Sep 16, 2011, 8:33 AM Jerry Beaucaire
Comments