Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Workdays – Across months DATE
  • Cells with additional spaces AND
  • Count cells with condition – multiple conditions COUNTIFS
  • DayName Date and Time
  • Change Calculator – calculate change in multiple bills/coins Formulas - combined functions
  • Shades of gray Graphics
  • Looplist – Repeating months using formulas Basic Math
  • Blank (isempty) VS “” (null string) Excel User tips

Dynamic selection list

Posted on February 7, 2012 By ANmar No Comments on Dynamic selection list

Using Index and CountA Formulas, Names and Forms > ListBox to have a list with connected formulas

Can also be used as form to read from a large table in another sheet

 

After creating two names, one is “Months” having:

=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,1)

And the other one is “Month_All” having:

=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,3)

Draw a listbox using “Developer toolbar”, then make its input list as “Months” name we did above, then use these formulas:

=INDEX(Months_ALL,E4,1)
=INDEX(Months_ALL,E4,2)
=INDEX(Months_ALL,E4,3)

To lookup the other fields once you select from the listbox

You can create a combobox from that “Developer toolbar” with exactly same features

ActiveX controls, COUNTA, Formulas - combined functions, INDEX, Lookup and References, Names, Standard functions

Post navigation

Previous Post: Validation based on another Validation
Next Post: WorksheetName and WorksheetsNo UDFs

Related Posts

  • Get column name (columnname as A,B,C, etc) as input inside cell ADDRESS
  • Validation based on another Validation COUNTA
  • CONVERT function CONVERT
  • Links between two workbooks – dynamically using functions INDIRECT
  • FORMULATEXT to show formulas FORMULATEXT
  • Searching table in 2 dimensions. Formulas - combined functions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • Week number to Month number DATE
  • Multiple Dual-Validations COUNTA
  • Search across sheets in function ADDRESS
  • Excel ad from early 90s Non-functions
  • CTRL+Y a mini macro Excel User tips
  • Get column name (columnname as A,B,C, etc) as input inside cell ADDRESS
  • Count Unique COUNTIF
  • Middle name + 1st name Basic Math

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme