Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • CHOOSE – tree of decisions CHOOSE
  • Workdays – Across months DATE
  • 0% interest promotion Basic Math
  • CountIf limitation COUNTIF
  • WorksheetName and WorksheetsNo UDFs Lookup and References
  • Links between two workbooks – dynamically using functions INDIRECT
  • Sort list dynamically (functions) COLUMN
  • GoLast to jump to last cell in a column CELL

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 Address into table Formulas - combined functions
  • Fixed length ID Formulas - combined functions
  • Dynamic selection list ActiveX controls
  • GoLast to jump to last cell in a column CELL
  • SimpleANBox Basic Math
  • Rotate table using 1 formula ADDRESS

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
  • Spreadsheet Compare application Excel User tips
  • CONVERT function CONVERT
  • Convert cell into textbox Excel User tips
  • Rotate table using 1 formula ADDRESS
  • Progress bar in pure functions CHAR
  • Count how many times letter found Formulas - combined functions
  • BMI Calculator Basic Math
  • Get 3rd Wednesday of month Basic Math

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme