Search Contextures Sites

 

Roger Govier's Excel Tutorials and Templates

Data Validation

DV0005 - Create Dependent Lists With Tables and INDIRECT -- Use Tables and the INDIRECT function, in this example, to create dependent drop down lists. There are versions with and without macros. Detailed Instructions  Sample File with NO Macros OR Sample File WITH Macros 04-Mar-14 

DV0004 - Create Dependent Lists With Tables -- As an alternative to using INDIRECT for creating a dynamic formula to define a range, you can use Tables. In this example, just 3 dynamic range names are used. There are versions with and without macros. Detailed Instructions  Sample File with NO Macros OR Sample File WITH Macros 03-Mar-14 

DV0003 - Create Dependent Lists With INDEX -- As an alternative to using INDIRECT for creating a dynamic formula to define a range, you can use the non-volatile INDEX function. In this example, just 4 dynamic range names are used. Three are used to create the basic framework of the method. The fourth permits any number of subsidiary lists to be created to act as Dependent dropdown lists from the entry in the previous column, without having to define individual names for each list. Detailed Instructions  RJG_Universal_DD_Dependent.zip 21kb 18-May-09

 DV0002 - Different Drop Downs from One Source -- Instead of using a different source for each data validation list, Roger Govier has created an Excel template with a method of using one formula to show different drop downs. RJG_Universal_DD.zip 39kb  22-Feb-09 

DV0001 - Add Headings for Navigation -- Add letter headings in a long list of data validation items, to make it easier for users to navigate the list. Excel template file from Roger Govier. DataVal_Headings.zip 15kb  13-Dec-06  

Functions

Names -- Create Dynamic Ranges With a Macro -- As an alternative to using OFFSET for creating a dynamic formula to define a range, you can use the non-volatile INDEX function. In this example the dynamic range names are used to create a PIVOT TABLE, and to show a similar type of report constructed using SUMPRODUCT. In the sample file there's a macro that will create the range names automatically for you.  15-Feb-09 

FN0001 - Treatment Calendar -- Enter a list of medication doses or injection sites, or another list. Click a button, to create a calendar with schedule of treatments. Sample Excel template from Roger Govier. Treatment Calendar.zip 24kb 11-Dec-08

Filters

FL0001 - Fast Filter -- Type criteria in the row above an AutoFilter, instead of selecting from the dropdown lists. The cells with criteria change colour, so you can easily see which columns have a filter applied, and see what the criteria are. Event code runs when a change is made on the worksheet. Sample Excel template from Roger Govier. Fastfilter.zip 32kb   10-Mar-08  

VBA

VB0001 - Monthly Workbook Creator -- Click a button, and the code in this Excel template creates a workbook for each month of the year, with a sheet for each day. Sample Excel template from Roger Govier. CreateMthlyWkbks.zip 26kb  08-Dec-08 

About the Developer

Roger Govier is an Excel MVP based in the UK who undertakes assignments in Excel and VBA for clients worldwide. While he enjoys the intellectual challenge of solving problems with worksheet functions, Roger claims to be intrinsically lazy, so he always looks for a fast and simple way to provide solid workable solutions.

You can contact Roger at:

roger@technology4u.co.uk
Technology 4 U, Glanusk Farm,
Llanfair Kilgeddin, Abergavenny, NP7 9BE, UK
Tel +44 (0) 1873 880266
Mobile +44 (0) 7970 786191


Roger Govier, Excel MVP

No help is available for the sample Excel templates. If you have comments or suggestions, please contact roger@technology4u.co.uk 
These sample Excel templates are provided "as is" for the sole purpose of illustrating Excel techniques. The authors do not warrant that the Excel templates will meet your requirements or that the operation of the Excel templates will be error free.

More Excel Templates

Contextures Files
Excel Samples -- AlexJ 
Excel Samples -- Roger Govier
Excel Samples -- Ron Coderre  
Excel Samples -- Norman Harker
Excel Samples -- Jerry Latham

Learn how to create Excel dashboards.
 

 

 

More Excel Files

Contextures Files

AlexJ
Ron Coderre

Roger Govier

Norman Harker

Jerry Latham

 

 

Contextures
Excel news
by email

 

 

30 Excel Functions in 30 Days

 

 

 

 

 

Last updated: March 4, 2014 2:54 PM

Privacy Policy