Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Excel Dynamic Drop-Downs that grow with you COUNTA
  • Recover the unrecoverable Excel User tips
  • GoLast to jump to last cell in a column CELL
  • OFFSET+MATCH Formulas - combined functions
  • Looplist – Repeating months using formulas Basic Math
  • Count Unique COUNTIF
  • Repeated Offset Basic Math
  • Multiple-Validations (connected to each other) Data Validation

Multiple Dual-Validations

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

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

This name will refer to the top header (which is the first validation in each of the sets)

Part2: is the Validation in column B, that list of Headers in sheet “Data”

Part3: which is the tough one is the Validation of column D, to have this formula

=OFFSET(First_Cell,1,MATCH(B3,Cat_MFR,0)-1,COUNTA(OFFSET(First_Cell,0,MATCH(B3,Cat_MFR,0)-1,10000,1))-1,1)

This one has to be set inside the Validation itself and not as name, because it holds related references based on that validation cell location.

Does that make sense?

Now you need to make sure that formula above is applied to cell D3, which is very critical to apply that validation considering the related references.

 

let me know if it worked or if you have questions

 

 

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

Post navigation

Previous Post: Multiple-Validations (connected to each other)
Next Post: Convert 2-column into wide (column-row) table

Related Posts

  • Cuts string of list of items Formulas - combined functions
  • Multiple lines in cell using functions CHAR
  • FORMULATEXT to show formulas FORMULATEXT
  • Multiple-Validations (connected to each other) Data Validation
  • Fixed length ID Formulas - combined functions
  • Standard math to calculate Aspect Ratio Basic Math

Leave a Reply Cancel reply

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

Recent Posts

  • Calculate above and below me
  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage

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 (83)
    • 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 (31)
      • AND (5)
      • IF (25)
      • IFERROR (7)
      • 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 (6)
      • Average (1)
      • 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
  • Schedule column Basic Math
  • IF with AND and IF with OR AND
  • Search across sheets in function ADDRESS
  • Count how many times letter found Formulas - combined functions
  • State Abbr with Index+Match+Validation Data Validation
  • Excel ad from early 90s Non-functions
  • Validation based on another Validation COUNTA
  • OFFSET+MATCH Formulas - combined functions

Copyright © 2026 Excel Functions.

Powered by PressBook News Dark theme