|
Home > Templates > Templates > Roger Govier Roger Govier's Excel Tutorials and WorkbooksExcel tutorials and free workbooks to download, provided by Roger Govier
|
Pivot TablesCreate Reports With GetPivotData Function -- Instead of using SUMIFS, COUNTIFS or AVERAGEIFS on the source data, you can quickly summarize data in a pivot table. Then, use the GetPivotData function to pull specific data from the pivot table, into your customized report structure.
Get a Unique Count With Pivot Table -- Roger Govier shows 3 ways to calculate a unique count (distinct count) with a pivot table. For example, with a count of 100 orders, how many distinct products were sold? |
Data ValidationDV0005 - 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 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 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 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 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 |
FunctionsNames -- 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. 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 |
FiltersFL0001 - 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 VBAVB0001 - 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. CreateMthlyWkbks.zip
|
About Roger GovierRoger Govier is an independent IT consultant based in Wales, assisting businesses with their Business Information (BI) requirements, making extensive use of Excel and Visual Basic. Roger also conducts Excel training for corporate clients, and at conferences. In 2007, Roger was honoured to receive an Excel MVP award from Microsoft, and he has recognized with that award every year since then. After completing his degree in Agricultural Economics and Business Management, Roger worked in the UK for both small and large public businesses, and ran his own business. In 1978, Roger became interested in programming, and from 1983 to 1997 was Commercial Director of a small software company in the UK. Roger returned to his native home of Wales in 1997, where he operates his consultancy business in Cardiff. His client base includes companies in the UK, USA, Canada, Brazil, Portugal, Switzerland and Australia. While he enjoys the intellectual challenge of solving problems with Excel worksheet functions, Roger always looks for a fast and simple way to provide solid workable solutions. In providing BI solutions with Excel, Roger uses Pivot Tables, Power Pivot, Power Query, VBA macros, and the most efficient functions for each task, including the latest Dynamic Array functions and Lambdas. You can contact Roger at: roger@technology4u.co.uk
|
Last updated: December 28, 2022 9:40 AM
