Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • New function IFERROR, finally IFERROR
  • Filter. Google Sheets function Google Sheets
  • IFS (and IFNA) to avoid nested IFs Formulas - combined functions
  • Fx to cut long column into two columns Formulas - combined functions
  • Excel Functions = XL f(x)s XLfxs
  • Blank (isempty) VS “” (null string) Excel User tips
  • Sorting with functions only CHOOSE
  • VLookup Lookup and References

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

  • Extract current (Active) workbook name CELL
  • Zipcode-State search Formulas - combined functions
  • Validation based on another Validation COUNTA
  • State Abbr with Index+Match+Validation Data Validation
  • Return 12 values in one number AND
  • Progress bar in pure functions CHAR

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
  • Unix DateTime Number Basic Math
  • Convert 2-column into wide (column-row) table Formulas - combined functions
  • Count cells with condition – multiple conditions COUNTIFS
  • Searching table in 2 dimensions. Formulas - combined functions
  • Show developer tab Excel User tips
  • Get 3rd Wednesday of month Basic Math
  • Get monthly total from table with dates DATE
  • Limited Ceiling Basic Math

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme