Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Tally chart in functions Formulas - combined functions
  • Lesson learned: Do not trust Google Sheets function Google Sheets
  • Show developer tab Excel User tips
  • Search across sheets in function ADDRESS
  • Validation based on another Validation COUNTA
  • Change Calculator – calculate change in multiple bills/coins Formulas - combined functions
  • Extract current (Active) workbook name CELL
  • SimpleANBox Basic Math

Category: COUNTA

Multiple Dual-Validations

Posted on February 9, 2012 By ANmar No Comments on Multiple Dual-Validations
Multiple Dual-Validations

It is to demonstrate how to do multiple validations in the same sheet, or as we want to call it

Mutliple-dual-validations

that depend on each other.

Checking out sheet “Data”, you can tell that Validations on column B is for the heads (Main categories), and on column D is for the sub category of the main that used in column B in that row.

Creating something like this basically falls in three parts

Part1: is the Names you need to define, so after we create the table found “Data” sheet …..

We need to set two names, one for the left-top cell for that table (used as reference) and another name with formula:

Read More “Multiple Dual-Validations” »

COUNTA, Data Validation, Formulas - combined functions, Lookup and References, MATCH, Names, OFFSET, Standard functions

Dynamic selection list

Posted on February 7, 2012 By ANmar No Comments on Dynamic selection list
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:

Read More “Dynamic selection list” »

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

Validation based on another Validation

Posted on February 7, 2012 By ANmar No Comments on Validation based on another Validation
Validation based on another Validation

Creating a Data Validation based on another Data Validation

Meaning that when you select from a drop down from the first the second will be filled with the list that is corresponding to it.

You need to create two names (Formula > Name Manager), one has this formula

=OFFSET(Sheet1!$A$2,0,1,1,COUNTA(Sheet1!$2:$2))

The other one has this

Read More “Validation based on another Validation” »

COUNTA, Data Validation, Formulas - combined functions, Lookup and References, MATCH, OFFSET, Standard functions

Excel Dynamic Drop-Downs that grow with you

Posted on January 29, 2012 By ANmar
Excel Dynamic Drop-Downs that grow with you

This will help you do a dynamic name to use on a drop down, a Form Listbox or Form Combobox. The screenshot almost says it all. You need 4 steps: 1- Create the name for a column in your Excel file, go to Names > Define in Excel2003 or earlier, or go to Formulas >…

Read More “Excel Dynamic Drop-Downs that grow with you” »

COUNTA, Data Validation, Formulas - combined functions, Lookup and References, OFFSET, Standard functions

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
  • Jan8 date format AND
  • Get 3rd Wednesday of month Basic Math
  • FORMULATEXT to show formulas FORMULATEXT
  • OFFSET+MATCH Formulas - combined functions
  • Count cells with condition – multiple conditions COUNTIFS
  • Links between two workbooks – dynamically using functions INDIRECT
  • WorksheetName and WorksheetsNo UDFs Lookup and References
  • Zipcode-State search Formulas - combined functions

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme