Smart Dependent List
(Or Cascading List, Dynamic Drop Down List)
Smart Dependent List is a fast, efficient and fresh approach to ensuring valid product selections, for products that have a variety of options, sizes or quantities.
Whilst typical Dependant Lists (or Cascading List) require a number of dynamic drop downs cells, for selecting a product, Smart Dependent List uses a single drop down, making it much easier and faster in day-to-day operations! Plus enabling additional capabilities and saving time and effort in set-up and maintenance of dependent lists. Smart Dependent Lists share the same great range of options as Smart Lists.
All Click My View tools are specially designed to help business users of Excel improve efficiency and productivity. They are for people who want to complete their work in minimum time, with minimum effort, ideally for it to be automated or just require a click. Smart Forms work straight away, with little or no time to set up and no technical skills are needed.hich
Smart Dependency Lists or Cascading Lists – a fresh new approach!
Users will find this new approach much faster and easier to work with than previous methods.
Day-to-day operational time will be significantly reduced with the ability to quickly zoom in on the target item and make just one selection. Time savings c great reduced effort is required to set up the dependent lists and maintain them.
First, let’s review the concept using the example of Food Produce. It is sold by type of produce, in both fresh and processed forms, in various types of packaging, by size, quantity or weight.
The green boxes are the only valid combinations. Intermediate selections are not acceptable.
Traditionally this required setting up a network of dependent tables. The example above requires 7 tables. Five of which are just for the combinations of how Artichokes can be sold!
The User then selects items from a series of dependent drop-down lists to arrive at a valid selection. In this example, it requires selecting items from 6 dependent drop down lists.
This is slow to use, slow to setup and slow to maintain. It also risks invalid user selection!
Smart Dependency Lists or Smart Cascading Lists – are a fresh new approach!
Selection of an item requires just one cell, only valid items are displayed for selection.
Below is an example of the multi-column display, with fast drill-down (by typing "art" the list was instantly reduced to just Artichokes) and viewing a photo (or recipe, specs.) of the item.
Smart Dependency Lists / Cascading List Are An Extension Of Smart Lists - They Include
-
Searchable List - the list is dynamically filtered, using the search text, as you type (see above)
-
Flexible Search Text - a letter, partial text, a word, phrase, or multiple words or phrases
-
Vertical Scroll Bar for fast scrolling up and down the list
-
Horizontal Scroll Bar to view long descriptions that are too wide to be displayed
-
Clear Selection with a click on a button
-
Easy to read - Size 12 font, (2 sizes larger) and twice as many items in the view (16)
-
Consistent text size - does not change size when zooming in out
-
Default settings - single selection, sort the list, remove duplicates from the list and hide hidden rows from the displayed list.
Fast Navigation To Next Cell:
-
OK button – default Excel cursor movement to select next cell (to go right or to go down)
-
Enter key - go to next visible row
-
Tab key - go to next visible column
-
Arrow keys – go left, right, up or down to the next visible cell
Other keys:
-
Delete - deletes cell content and exits, remaining in the active cell
-
Escape – exits, leaving unchanged, remaining in the active cell
Options include:
-
Show Hidden Rows - show hidden rows in the list
-
Multiple Selections - enable multiple selections of items
-
Add New Item - allow users to add items to the list on-the-fly
-
Display Photo - allow users to open photo, document or web page
-
Do Not sort the list - display items as found
-
Include Duplicates In The Displayed List- display all items
Local Photos and Images are displayed in a special window (see example)
Local PDF's / Office Documents / Files open in relevant application
Web Pages / Web Links are opened in the default browser.
Smart Dependency Lists/Cascading Lists setup
Step 1 - Create A Dependency Table: containing one row for each valid/selectable item combination.
Columns in this table are similar to a typical product table, containing an item ID, a description and columns for each of the dependency hierarchy elements. Perhaps also a quantity on hand, price and notes such as lead time, etc.
Each row must be a valid combination of hierarchical levels that enable the item to be selectable.
For example, Food Type could be Vegetables, Poultry, Seafood, etc.
Vegetables | Artichoke | Fresh | Per Kg
Vegetables | Artichoke | Pickled | Jar | 100g | Each
Vegetables | Artichoke | Pickled | Jar | 100g | Per Case
Vegetables | Artichoke | Pickled | Jar | 500g | Each
Vegetables | Artichoke | Pickled | Tin | 1Kg | Each
There are many benefits to this structure and approach:
-
All key columns can be displayed at once, making items easier to see and select.
-
Items are searchable across all descriptors, using partial text, single and multiple words.
-
One drop-down cell is used to minimize time and effort.
-
Lightning-fast drill down is now possible, bypassing intermediate levels. In the example, by just typing "art", the list instantly shrinks to Artichokes and with one click the item is selected.
-
Setup and maintenance are easier and faster than traditional methods.
In addition to the main table columns, there are optional columns to the left of the drop-down cell range. Termed invisible columns (they are not visible in the displayed list) they consist of:
-
Column - 1: Typically a Product ID that can be returned as the value (or used by lookup).
-
Column -2: Perhaps a tailored description that can be returned.
-
Column -3: A link or hyperlink to display a photo or image, open a document or pdf or open an intranet/internet web page or object on a page.
-
Local Photos and Images are displayed in a special window
-
Local PDF's / Office Documents / Files open in relevant application
-
Web Pages / Web Links are opened in the default browser.
-
Step 2 - Create a Range Name: including the option codes for Multi-Column Display and Data To Be Returned. The cell range to be specified is the first column of the table that will be displayed.
Step 3 - Add Date Validation to the cell: entering the range name in Data Validation Source field
__________________________________
Example Of Range Name Setup
Multi Selection, Multi Column, Return Invisible Product ID
Existing Data Validation Range = Product Name cells (G3:G150)
1. Create Range Name "=CMV_1VY_ProductName" constructed as:
-
CMV_ = Smart Form options prefix
-
1 = Multi selection, with comma separated return value
-
V = Multi column display - infinite (ends with a blank cell)
-
Y = Return Range Column -1 (Product Id is to left of Name)
-
_ = Smart Form options suffix
-
"ProductName" - your choice - e.g. cells contents name
-
Set Refers To Field: to existing data validation range (G3:G150)
2. Set Validation Source Field To: =CMV_1VY_ProductName
Detailed User Manual And Comprehensive Example Workbook Included
Smart Forms Control Menu
Overall control of the Smart Forms is through a ribbon menu, entitled CMV
-
About: Displays a message box containing information about the software version
-
Start Functions: Starts all functions – checks the licence and awakes the functions.
-
Stop Functions: Stops all functions – puts them to sleep.
-
Backup File: Creates a backup of your workbook with a time-stamped file name.
-
Sheets: Opens the Sheets window for fast navigation to hidden sheets.
-
Menus On - Off: Enables / disables automatic display of menus.
-
Lists On - Off: Enables / disables automatic display of Smart Lists / Dependant Lists.
-
Calendar On - Off: Enables / disables automatic display of cell calendars.
-
Smart Text On - Off: Enables / disables automatic display of text editing windows.
Please join our mailing list for news and updates
Smart Forms Productivity Tools
Helping every-day Excel users to work faster, more efficiently and increase spreadsheet capabilities.
-
Smart List - automatically replaces Excel drop downs, searchable, multi-column display, multi-selection. With numerous options, which includes viewing photos, documents, web pages and enabling Smart Dependency Lists – with a new approach, that is much faster and easier than traditional methods
- Smart Cell Calendar – no more blindly typing dates – with fast selection of real working days, etc.
-
Smart Dependency List – a new, much faster and easier method, than traditional methods
-
Smart Text Edit – an automatic window for cell text editing - with basic formatting, real tabs, bullets, etc.
-
Smart Menu - intelligent menu - go to any cell - auto opens workbooks, sheets and runs macros
-
Smart Sheets – click to go to any sheet - hide/unhide individual sheets or all sheets with just a click
This is a short overview of Smart Forms
"Smart Forms save me time!"
Customer Satisfaction Is Important To Us
We believe in the effectiveness and quality of our products and are confident they will save you time and ease your workload.
If for any reason you are not satisfied within 30 days, we will happily return your money.
Guaranteed!
Smart Forms Pack Inclusions
-
Smart Lists – replaces Excel drop downs, searchable, multi-column display, multi-selection. With numerous options, which includes enabling Smart Dependency Lists – which is a new approach, much faster and easier than traditional methods
-
Smart Text Editor – for reading & editing text - with basic formatting, real tabs, bullets, etc.
-
Smart Cell Calendar – no more blindly typing dates – fast selection of the day, month & year
-
Smart Menu - intelligent menu - click to open workbooks, unhide sheets & cells, run macros
-
Smart Sheets – click to go to any sheet, plus click for fast hiding / unhiding of sheets
-
Free point release updates, support, User Manual, Examples workbook, Access to all Videos
Plus a Free AddIn for Cell & Sheet Magnification!