Smart Drop Down Support
Please Bookmark This Page
And
Scroll Down For Instructions
Fast Searching Using Partial Words, Multiple Words and Multiple Phrases - Within All Displayed Cells
Dynamic Search Reduces List To Just Rows Matching The Search Criteria
Faster, Easier Selection Of Drop Down Items
Ideal for Long Lists, Random Sequence or With Duplicates
Larger Drop Down Font For Easy Reading
Multiple Columns In View For Improved Understanding & Accuracy
Multiple Selections For Faster Selection
Instantly Change To Smart Drop Down - Simply Install Smart Drop Down And Excel Drop Downs Are Automatically Replaced
-
Clicking SmartXUser in the top ribbon opens the Smart Drop Down menu.
-
By default Smart Drop Down automatically opens whenever the user selects a cell that has "List validation" (drop down).
-
To switch off Auto opening of smart drop down, click the Off button.
-
To switch on Auto opening of Smart Drop Down, click the Auto button.
Drop Down Function Buttons
Add New Row
Hide / Show Duplicates
Sort List
Using Drop Down Options
Both Multi Option and Pro Option licences enable far greater functionality than normal Excel Drop Down lists.
Options are requested by:
a) Defining a name for the data validation range, and embedding the required option codes within the name.
b) Entering this range name in the Data Validation Source Field.
The structure of a Drop Down Option Range Name is:
SXU_xxx_YourRangeName
SXU_
Specifies it is an option range name
xxx_
represents one or more Option Codes
Note: that "_" ends both of the above
YourRangeName
makes it meaningful and unique
Smart Drop Down "Multi" Options
In addition to all Smart Drop Down Lite Features:
-
Enable Multiple Selections - enable selections of multiple rows
-
Display Multiple Columns - 1-4 columns e.g. product code & name
-
Choice of CSV Separator - for returning Multiple Selections
-
Add New Item - allow users to add items to the list on-the-fly
-
No sort - display rows as found - overrides default setting
-
Show/Hide Duplicates - overrides default setting
-
Hide Hidden Rows - override default setting
"Multi" Option Codes
-
Show Multiple columns (validation range plus cells to right)
-
S - Show 2 columns in the list
-
T - Show 3 columns in the list
-
U - Show 4 columns in the list
-
(Up to 99 columns are available in the Pro version)
-
-
Enable Multiple Selections From Drop Down List
All Selections Are Returned Into One Cell With A Separator Between Each Value: e.g. value1, value2, value3
-
1 - Comma + Space
-
2 - Comma only
-
3 - Semi Colon + Space
-
4 - Tab
-
5 - New Line,
-
6 - Colon + Space
-
7 - Period + Space
-
8 - Space + "/" + Space
-
9 - Space + "-" + Space
-
-
Add New Item to the list
-
A - Add new item to list (use this for Excel tables)
-
B - And Auto adjust the range
-
C - And sort table (to saves you time)
-
-
Override Options - these override the Default Configuration
-
Hidden Rows
-
H - Hide Hidden Rows (e.g. hide out of stock items, new products not yet ready)
-
-
Duplicates (Duplicate Rows)
-
J - Hide Duplicate rows
-
K - Show Duplicate rows
-
-
Sorting the list
-
L - List the rows in A-Z sequence
-
N - No sorting of list (display rows as found)
-
-
Example
-
To Display 3 columns ("T") and Enable Multiple selections - returning each value, separated by a comma + a space ("1") first create the range name SXU_T1_AccountMgr for the cell range $U$2:$U$53
-
Now replace the cell range within the Validation Source field with =SXU_T1_AccountMgr.
.jpg)
Smart Drop Down "Pro" Options
In addition to all Smart Drop Down Lite and Multi Options Features, the Pro Options include:
-
Display Variable Number Of Columns (up to 99)
-
Display Photo or another type of object - for users to view a photo, image, pdf, web page or even open a document or spreadsheet
-
Return a different value to the traditional 1st column of the range
Pro Options enable additional, Invisible Columns to be used: e.g.
-
Internal Product Key or Internal Description
-
Alternative Product Description or other key information
-
Link to the object to be displayed to the user - photo, file, web page.
Pro Options also enable much simpler management of Dependent Drop Down / Cascading Drop Down lists. All the items are held, managed, viewed and searched within a single structured table. Thus avoiding the complexities of managing multiple drop down cells and scores of individual lists. It's also faster to use a single drop down to search and find items across all types, categories, locations, etc.
Details Of "Pro" Options
.jpg)
Pro Options enable greater flexibility, with a variable number of columns (up to 99) in view and access to information that is not visible in the list.
-
Column -1: Text - e.g. Internal Product Key or Description
-
Column -2: Text - e.g. Alternative Description, Notes
-
Column -3: Link - e.g. image / document / pdf path name or hyperlink to a web page
-
Display a variable number of columns (up to 99)
-
V - Cells up to the first null cell in each row are displayed
-
-
Display Photo or another object
-
P - Uses the link in column -3 as the object to open
photo, pdf, web page or open a document or spreadsheet
-
-
Return a different column value (instead of the 1st column)
-
R - Returns text concatenated from all values in the row
-
W - Returns Column 2 of the view (e.g. Product ID)
-
X - Returns the last column of the row
-
Y - Returns Invisible column -1
-
Z - Returns Invisible column -2
-
EXAMPLE:
-
Enable Multiple selections - returning each value, separated by a comma + a space ("1") ,
-
Display a Photo, Image, document or web page associated with the item ("P").
-
Display a variable number columns, depending on the item in the row ("V")
-
Return Internal Product ID from invisible column -1 ("Y")
-
Create the range name SXU_1PVY_ProductID for the cell range $U$2:$U$53
-
Now replace the cell range within the Validation Source field with SXU_1PVY_ProductID
Button Descriptions (shortcut)
Cancel (C) - Quit Editing and return to the cell without saving the selection.
Reset (R)- Clears all current selections in the list
OK (O) – saves selection and uses the system default Excel cursor movement to move to and select the next cell (it either goes right or down one cell)
Select All (A) - Selects All Rows in the list (this button is only visible when multiple selection lists are requested).
Delete (D) - Deletes the actual content within the cell and clears all selections. (Delete cannot be undone).
Show Photo (S) - Displays the nominated photo, image, pdf file, web page, document or spreadsheet (this button is only visible when option P has been requested)
Keyboard Actions
Enter key - saves selection and moves to next visible row
Tab key - saves selection and moves to next visible column
Arrow Keys When Cursor Is NOT In The List
Up and Down arrows - saves selection and moves to the next visible row above or below on spreadsheet.
Left and Right arrows – saves selection and moves to next visible column to the left or right on spreadsheet.
Arrow Keys When Cursor Is In The List
Up and Down arrows - move to list row above or below
Left and Right arrows – save selection and moves to next visible column to the left or right on spreadsheet.
Escape key - Quits Editing and returns to the cell without saving the selection.
Click X top right - Quits Editing and returns to the cell without saving the selection
Smart Drop Down Functions
SXULIST_GetMultiSelectionN
Returns one selection from a multi-selection cell. This enables the extraction of each selection from within a multi-selection cell.
For example:
B2 is the multi-selection cell (e.g. Apple, Pear, Lemon)
B5 is to hold the first selection - Apple
B6 is to hold the second selection - Pear
B7 is to hold the third selection - Lemon
In B5 enter:
=SXULIST_GetMultiSelectionN(B2,ROW()-ROW(B$4),",")
Copy formula down to B6, B7
If there is nothing for a row it returns null
SXULIST_GetCellValidationType
Returns the type of cell validation.
=SXULIST_GetCellValidationType(A1)
Returned values are:
-
0 No Validation
-
1 Whole numeric values Integer
-
2 Numeric values Number
-
3 Value in a list List
-
4 Date values Date
-
5 Time values Time
-
6 Length of text
-
7 Data is validated using an arbitrary formula
SXULIST_GetCellFormatType
Returns the cell format string
=SXULIST_GetCellFormatType(A1)
Excel returns:
-
General = "General" = 0
-
Number = 1
-
Percentage = "%" = 2
-
Year = "yy" = 3
-
Month = "m" = 3
-
Day = "d" = 3
-
TimeH = "h" = 4
-
TimeHM = "hm" = 4
-
TimeMS = "ms" =4
-
TimeS = "s" =4
-
Text = "@" =5
Bonus Smart Drop Down Functions
These functions can be called from cells or from VBA.
-
SXU_CleanString - Thoroughly cleans out un-printable characters
=SXU_CleanString (text string)
-
SXU_Beep – Sounds System Beep (call it when a major rule fails)
=SXU_Beep()
-
SXU_FileExists - Returns true if the file exists in the current folder
=SXU_FileExists(file name)
-
SXU_SheetExists - Returns true if the Sheet exists
=SXU_SheetExists(sheet name)
-
SXU_GetAddress - Returns the range of a named range as string
=SXU_GetAddress(range)
-
SXU_GetActiveCellAddress - Returns active cell address as string
=SXU_GetActiveCellAddress()
-
SXU_GetCellFormula - Returns the formula as string
=SXU_GetCellFormula(range)
-
SXU_GetCellFormulaAll - Returns Address, Contents and Formula
=SXU_GetCellFormulaAll(range)
-
SXU_GetHyperLink - Returns hyperlink of a cell.
=SXU_GetHyperLink(range)
-
SXU_GetLastRowInColumn - Returns the last used row number
=SXU_GetLastRowInColumn(column number)
-
SXU_GetLastColumnInRow - Returns last used column number
=SXU_GetLastColumnInRow(row number)
-
SXU_GetPath - Returns the path of the current workbook
=SXU_GetPath()
-
SXU_GetSystemDateFormat - Returns system date format
=SXU_GetSystemDateFormat()
-
SXU_IsMac - Returns TRUE if this is a Mac
=SXU_IsMac()
System Requirements
Installation & Registration
Product Activation
Click SmartXUser Menu
Then Click About Button
Click Yes Button
Click Register / Activate Product Button
Activating The Product
The link to download the software, and your activation code are contained in the email that you received.
A valid activation code is required to enable the software to function.
A new activation code is required when upgrading or renewing the license.
Enter Activation Code and click OK
Configuration Options
The default configuration represents the attributes of a normal Excel Drop Down
-
Sort Down List: Default is no sort. Ascending ensures that all lists appear in A-Z sequence
-
Hide Duplicates: Default is to show all rows. Checking Hide duplicates ensures that all lists have duplicates removed.
-
Width Expansion Units: Number of pixels to expand the drop down for an extra column
-
Font Size Increment: Smart Drop down uses the same font and font size as the drop down cell for displaying the list. This Number provides a default size increase or decrease in font size for the text being displayed in all Smart Drop Down lists.
-
String Separator: This separator is used when displaying multiple columns in the drop down.
-
Arrow Keys Exit and Go To Next Cell: When checked you can use the arrow keys for faster movement from the drop down with up, down, left or right. If a selection has been made, the selection is saved before moving to the next cell.
We guarantee that our software is virus-free, malware-free, trojan-free and does not obtain or upload user information.
Should you encounter a warning from your Antivirus software, please advise us and we will send our software to your Antivirus software provider for confirmation that detection is incorrect and for them to issue an update to avoid further false warnings.
