DV0062 – Data Validation Click Combobox
– Add New Items — Click a cell that contains a data validation
list, and a combobox appears — font size can be set, more than
8 rows displayed, autocomplete can be enabled. If a new item is
entered, you will be asked if you want to add it to the existing
list. DataValCombobox_AddSort_Multi.zip
28kb 12-Dec-13
DV0061 – Assign Players Each Inning
— Select a player from a dropdown list in this Excel template,
and that name disappears from the other dropdowns for that inning.
Excel 2010/2013: DataValPlayerInnings2013.zip
22kb 08-Dec-13
DV0060 – Dependent List From Row
Items — Select an employee name from the first drop down,
second drop down shows all skills for that employee. ListsDependentRow.zip
32 kb 24-Sep-13
DV0059 – Add New Items to Validation
List — In a workbook with multiple data validation lists in
Excel tables, type a new value in a cell that contains data validation,
and confirm that you want to add it to the drop down. It’s added
to the appropriate source list, and the list is sorted; a macro
automates the list updates. DataValListAddSort_Tables.zip
32 kb 24-Sep-13
DV0058 – Limit Budget Entries with
Data Validation — Limit the total amount that is entered in
an Excel budget worksheet, by using data validation to check the
total. Create a custom message that appears when the amount goes
over budget. You can also see the unbudgeted amount that is still
available, and adjust the entries if required. DataVal_BudgetTotal.zip
12 kb 25-Jul-12
DV0057 – Data Validation Combobox
Codes — Double-click a cell that contains a data validation
list, and a combobox appears, showing a list of descriptions. Select
a descriptions, and that descriptions numeric code is entered in
the cell. Lists are stored in named ranges on a separate sheet.
Excel 2007/2010 format; macros must be enabled. DataValComboboxCodes.zip
30 kb 02-May-12
DV0056 – Dynamic Data Validation
— With this data validation technique from AlexJ, users can
see a drop down list with just the top projects, or all projects.
A macro cleans up the selection cell, if necessary. AlexJ_DynamicDataVal.zip
16 kb 16-Feb-11
DV0055 – Dependent Data Validation
Click Combobox — Click a cell that contains a data validation
list, and a combobox appears — font size can be set, more than
8 rows displayed, autocomplete can be enabled. List in second column
is dependent on selection in first column. DataValComboClickDepend.zip
18 kb 25-Feb-10
DV0054 – Create Dependent Lists
With INDEX — As an alternative to using INDIRECT for creating
a dynamic formula to define a range, Roger Govier shows how 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
DV0053 – Different Drop Downs from
One Source — Instead of using a different source for each data
validation list, AlexJ has devised a simple way to use the same
source for all the lists. For details see Contextures Blog article
Different
Excel Drop Downs from One Source. APJ_Universal_DD.zip
25kb 22-Feb-09
Roger Govier has created an Excel template with another method of
using one formula to show different drop downs. RJG_Universal_DD.zip
39kb 22-Feb-09
DV0052 – Show or Hide User Tips
— Excel template from AlexJ uses data validation to show messages
for users on a worksheet. Users can choose from a drop down list
to show or hide the messages. For details see Contextures Blog article
Show
or Hide User Tips in Excel. APJ_ToolTips.zip
9kb 11-Feb-09
DV0051 – Assign Players Each Inning
— Select a player from a dropdown list in this Excel template,
and that name disappears from the other dropdowns for that inning. DataValPlayerInnings.zip
3kb 12-May-08
DV0050 – Data Validation Lookup
— Select a level from a dropdown list in this Excel template,
then enter a minimum and maximum value in adjacent columns. The
values are constrained by the limits set in a lookup table on another
worksheet. DataValMinMax.zip 3kb 01-Jul-07
DV0049 – ClipArt Selection —
Select a clipart item from a data validation dropdown list, and
that picture appears in the adjacent cell. Uses Worksheet_Calculate
event code. Excel template from Bernie Deitrick. ClipArtEvent.zip
30kb 03-Jun-07
DV0048 – Dynamic Dependent Dropdowns
from Unsorted List— Select Yes or No from the first dropdown
list in this Excel template, and the dependent cell’s validation
list shows only items for that selection. Dependent lists are created
from an unsorted master list, where items are marked as Yes or No.
DataValDynamicUnsorted.zip
8kb 23-Mar-07
DV0047 – Hide Previously Used Items
in Dependent Lists — Limit the choices in a Dependent Data
Validation list, hiding items that have been previously selected
in this Excel template. DataValHiddenDepend.zip
4kb 10-Feb-07
DV0046 – 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
from Roger Govier. DataVal_Headings.zip
15kb 13-Dec-06
DV0045 – Hide Matching Columns —
Select a date from a dropdown list in this Excel template, and columns
with matching date in the heading are marked. Click a button to
hide marked or unmarked columns. HideMarkedCols.zip
10kb 25-Nov-06 Updated 05-Dec-06
DV0044 – Dependent Cell List Multiple
Offset— Select a name from the first dropdown list, and the
dependent cell’s validation list shows only products for that name.
Select a product, and the third column’s validation list shows only
the items for that name and product. Based on a lookup table sorted
by name and product. DataValMgrProdClass.zip
6kb 29-Sep-06
DV0043 – Data Validation Combobox
With Entry Check — Double-click a cell that contains a data
validation list, and a combobox appears — font size can be set,
more than 8 rows displayed, autocomplete can be enabled. When the
combobox loses focus, the entry is validated. DataValComboCheck.zip
15 kb 29-Jun-06 Updated 11-Oct-06
DV0042 – Limit Data Validation Selection
— If a selection has been made in a dependent cell in this
Excel template, the data validation list is limited to the current
selection. DataValListLimit.zip
3 kb 26-May-06
DV0041 – Reset Data Validation Cells
— Macro to clear data valiation cells, and if cell has a data
validation list, select the first item from the list in this Excel
template. DataValReset.zip 10 kb
21-May-06
DV0040 – Assign Qualified Employees
to Single Task Per Day— Lists of employees qualified for each
task are created. Names are removed from data validation dropdown
list once they’ve been assigned to any daily task in this Excel
template. DataValDealers.zip 10 kb
15-Apr-06
DV0039 – Seating Plan — Use
data validation and shapes linked to cells, to plan table seating
arrangement. SeatPlan.zip 6kb
17-Mar-06 Instructions NOTE: Newer version here
DV0038 – Flexible Item List —
Use formulas to automatically add new unique items to a data validation
dropdown list. Excel template from Ron Coderre. DataValFlexList.zip
7kb 18-Feb-06
DV0037 – Dependent Lists Country
City— Select a country from the first dropdown list in this
Excel template, and the dependent cell’s validation list shows only
the cities in that country. To prevent invalid data, after a city
is selected, the country dropdown shows only that city’s country.
DataValCountryCity.zip 6kb
10-Oct-05
DV0036 – Dependent Cell List Offset—
Select a region from the first dropdown list, and the dependent
cell’s validation list shows only the customers in that region.
Based on a lookup table sorted by Region. DataValRegionCust_2003.zip
7kb 31-Mar-05 Detailed
Instructions 10-Oct-05
DV0035 – Dynamic Chart — Select
Start and End dates from Data Validation lists; chart updates automatically
to display sales for selected time period. ChartDateAuto.zip
5kb Detailed
Instructions 17-Jun-05
DV0034 – Input Message in Textbox
— Select a cell that contains a data validation input title
or message, and a textbox appears. Control the size, position and
formatting of the message. DataValInputMsgAlt.zip
11 kb 25-Apr-05 Detailed
Instructions 05-Jun-05 Works on
protected sheet. updated 08-May-06
DV0033 – Dependent ShipTo Location—
Select a customer, then select a shipping location from the dependent
dropdown. VLookup formulas return the shipping address for the selected
location. DataValShipTo.zip 4
kb 19-Apr-05
DV0032 – Dependent Data Validation
Combobox — Double-click a cell that contains a data validation
list, and a combobox appears — font size can be set, more than
8 rows displayed, autocomplete can be enabled. List in second column
is dependent on selection in first column. DataVal_Combo_Depend.zip
12 kb 12-Apr-05
DV0031 – Create Chart from Current
Data — Select a value from a data validation dropdown, and
an event procedure captures the current data, and creates a scatter
chart. DataValChartCreate.zip
12kb 07-Apr-05
DV0030 – Dependent Cell List Lookup—
Select an item from the first dropdown list, and the dependent cell’s
validation list shows only the accessories available for that item.
Based on a lookup table where applicable accessories are marked.
DataValLookupList.zip 7kb
DV0029 – Dependent Cell Examples—
Two samples of code altering dependent cells. Source lists on separate
sheet.
1) Selection from two lists must be different, or cell is cleared
2) Select from either list, and adjacent cell is filled in. Based
on code from Jason Morin. DataValCode02.zip
12kb updated 24-Apr-06
DV0028 – Data Validation Combobox
Named Lists — Double-click a cell that contains a data validation
list, and a combobox appears — font size can be set, more than
8 rows displayed, autocomplete can be enabled. Lists are stored
in named ranges on a separate sheet. Macros must be enabled. DataValComboboxSheet.zip
13 kb updated 15-Jan-07 View detailed
Instructions
DV0027 – Update Dependent Cell
— Selection from first list runs event code that selects the first
item from a dependent list in an adjacent cell. Based on code from
Jason Morin. UpdateDependent.zip
9kb 20-Feb-05 updated 21-Dec-11
DV0026 – Variable Validation
— Selection from the first list runs event code that changes the
validation in adjacent cell. DataValYesNoClear.zip
11kb 16-Jan-05
DV0025 – Data Validation Spinner
— Use a spin button control to select the next or previous item
from a data validation list. One example uses a macro, the other
example uses a formula DataValSpinner.zip
30kb
DV0024 – Cooking Time Planner
— Plan your meal preparation, using Data Validation to enter food
items. Formulas calculate the cooking schedule, based on the target
meal time. DinnerPlanner.zip 13kb
updated 08-Oct-06
DV0023 – Limit Selection List
— If you have a lengthy list, it’s hard to find an item in the
data validation dropdown. Use an Advanced Filter to create a short
list, and select from that. Event code creates the short list. DataValSelectName.zip
13kb
DV0022 – Update Validation Selections
— If you change an item in a data validation source list, the worksheet
may show previously selected items. Event code can update the worksheet
when you update the source list.
Multiple lists: datavalupdatemulti.zip 68kb 22-May-18
One list: DataValUpdate.zip
10kb Updated 11-Sep-07
DV0021 – Update Multiple Validation
Lists — In a workbook with multiple data validation lists,
type a new value in a cell that contains data validation, and it’s
automatically added to the appropriate source list, and the list
is sorted; a macro automates the list updates. DataValListAddSort_Multi.zip
12 kb Detailed
Instructions Updated 10-Apr-10
DV0020 – Data Validation Combobox—
Double-click on a cell that contains a data validation list, and
a combobox appears — font size can be set, more than 8 rows displayed,
and autocomplete can be enabled. Macros must be enabled. DataValCombobox.zip
16 kb Detailed
Instructions updated 15-Jan-07
DV0019 – Default to First Value—
Data validation limits values that can be entered; event macro enters
the default value for the selected option. DataValYesNoRates.zip
8 kb
DV0018 – Happy Face Gauge—
Data validation limits values that can be entered; event macro adjusts
the curve. Separate worksheets with code for Excel 2003 and Excel
2007. Also see sample file UF0050 Download file: HappyFace.zip 8
kb
DV0017 – Select Multiple Items from
Dropdown List— Select multiple items from a dropdown list;
an event macro stores selections in adjacent cell, or in same cell.
DataValMultiSelect.zip 18kb
updated 16-Jan-14 Instructions
DV0016 – Assign Employees to Single
Task Per Day— Names are removed from data validation dropdown
list once they’ve been assigned to a daily task. DataValDailyList.zip
6 kb
DV0015 – Create Dependent List for
Selected Column — The first dropdown list is based on column
headings. The second list contains unique items from the selected
column, sorted in descending order. DataValListSort.zip
11 kb
DV0014 – Combine Multiple Lists
into One — A data validation list from a worksheet must come
from contiguous cells in a single column or row. This example uses
formulas to combine three dynamic lists into one master list. DataValMultiLists.zip
3 kb
DV0013 – Cross Dependent Validation
Lists — Selection from the first list controls the items available
in the dependent cells. Selections in the dependent lists control
the dropdown items in the first list. DataValYesNoDepend.zip
3 kb
DV0012 – Update Validation List
— type a new value in a cell that contains data validation, and
it’s automatically added to the source list, and the list is sorted;
a macro automates the list updates. DataValListAddSort.zip
11 kb
DV0011 – Dynamic Validation List
— shows customers with start and end dates that include selected
date, macro automates the list creation. DataValDateRange.zip
12 kb
DV0010 – Model Pricing Scenario
— uses data validation to create dropdown lists, Scenarios to store
variables, macro automates scenario display. ScenarioParts.zip
13 kb
DV0009 – Purchase Order — uses
data validation to create dropdown lists, VLookups to return values
from named ranges on different sheets. VLookupNamedRange.zip
7 kb
DV0008 – Data Validation Checklist
— uses data validation to create dropdown lists, with only checked
items appearing in the list. DataValDynaChk.zip
4 kb
DV0007 – Chart Selected Date Range
— uses data validation to create dropdown lists, and dynamic named
ranges to plot the selected date range. ChartDateRange.zip 12 kb 03-May-09
NOTE: See version in Charts section: CH0013
DV0006 – Machine Capacity —
uses data validation to create dropdown lists, and the VLookup and
Match functions to extract information from a table. MachineCapacity.zip
3 kb
DV0005 – Data Validation “Columns”
— Data Validation dropdown displays product name and ID; an event
procedure changes the selection to product name. (XL2000 +)
DataValNameID.zip 9
kb; (XL97) DataValNameID97.zip
11 kb
DV0004 – Data Validation Change
— Select a Product from the Data Validation list; an event procedure
changes the product name to a product code. DataValCode.zip
8 kb DataValCode2007.zip
22 kb
DV0003 – Order Form — Select
items from the dependent Data Validation lists; a VLookup formula
extracts the unit price. DataValOrder.zip
11 kb
DV0002 – Invoice for Selected Number
— uses Data Validation and VLookup to extract details for an invoice
DataValVlookup.zip 8
kb
DV0001 – Assign Employees —
ensure that each employee is only assigned once per day
AssignEmp.zip 12 kb
No comments:
Post a Comment