Smart Drop Down Support

Please Bookmark This Page
And
Scroll Down For Instructions 

Tick quality-small.png

Fast Searching Using Partial Words, Multiple Words and Multiple Phrases - Within All Displayed Cells

Tick quality-small.png

Dynamic Search Reduces List To Just Rows Matching The Search Criteria

Tick quality-small.png

Faster, Easier Selection Of Drop Down Items

Tick quality-small.png

Ideal for Long Lists, Random Sequence or With Duplicates

Tick quality-small.png

Larger Drop Down Font For Easy Reading

Tick quality-small.png

Multiple Columns In View For Improved Understanding & Accuracy 

Tick quality-small.png

Multiple Selections For Faster Selection 

Tick quality-small.png

Instantly Change To Smart Drop Down - Simply Install Smart Drop Down And Excel Drop Downs Are Automatically Replaced

NOTEPAD SmartXUser Menu.png
Drop Down Menu.png
  • 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.

Product -Drop Down Multi.png
Gold_star 18kb dreamstime_xxl_4829129-re
Drop Down Function Buttons
Drop Down Function Buttons.png
Drop Down Function Buttons.png
Drop Down Function Buttons.png

Add New Row

Hide / Show Duplicates

Sort List

Gold_star 18kb dreamstime_xxl_4829129-re
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

Gold_star 18kb dreamstime_xxl_4829129-re
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

  1. 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

  2. Now replace the cell range within the Validation Source field with =SXU_T1_AccountMgr.

List 1 - Validation Source Range.png
My Arrow Down Left.png
Gold_star 18kb dreamstime_xxl_4829129-re
Coming Soon (2).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
Coming Soon (2).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")
     

  1. Create the range name SXU_1PVY_ProductID for the cell range $U$2:$U$53

  2. Now replace the cell range within the Validation Source field with SXU_1PVY_ProductID

Gold_star 18kb dreamstime_xxl_4829129-re
Button Descriptions  (shortcut)
Drop Down Window Buttons.png

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)

Gold_star 18kb dreamstime_xxl_4829129-re
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  

Gold_star 18kb dreamstime_xxl_4829129-re
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

Gold_star 18kb dreamstime_xxl_4829129-re
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()

go-sign-small.png
System Requirements
go-sign-small.png
Installation & Registration
Gold_star 18kb dreamstime_xxl_4829129-re
Product Activation

Click SmartXUser Menu

 

Then Click About Button

NOTEPAD SmartXUser Menu.png
Drop Down Menu.png
MyArrowRight.png
Drop Down About.png
arrow_right_blue.png

Click Yes Button

MyArrowRight.png
Drop Down Options.png
arrow_right_blue.png

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.

Registration Window.png
MyArrowRight.png
arrow_right_blue.png

Enter Activation Code and click OK

Gold_star 18kb dreamstime_xxl_4829129-re
Configuration Options
Drop Down Options.png

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.

Gold_star 18kb dreamstime_xxl_4829129-re
FAQ_Button small.png

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.

Virus clean small.png
Virus clean small.png