Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Fx to cut long column into two columns Formulas - combined functions
  • Excel Dynamic Drop-Downs that grow with you COUNTA
  • Walking Columns ActiveX controls
  • Unix DateTime Number Basic Math
  • Text-to-Columns, dynamically using formulas IFERROR
  • Always get 1st name (or last) Formulas - combined functions
  • Get 3rd Wednesday of month Basic Math
  • Calculate hours between two cells ABS

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

  • GoLast to jump to last cell in a column CELL
  • Sorting with functions only CHOOSE
  • WorksheetName and WorksheetsNo UDFs Lookup and References
  • The Excel “&” bug Excel User tips
  • CONVERT function CONVERT
  • Min Max vehicle Formulas - combined functions

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
  • UDF – Convert Number into text (English and Arabic) User-Defined f(x)s = UDF
  • Paste Special Percentage Excel User tips
  • Calculate above and below me Average
  • Get column name (columnname as A,B,C, etc) as input inside cell ADDRESS
  • Week number to Sunday date – Weeknum reverse DATE
  • Excel sessions – 2013 VS 2010 Excel User tips
  • Hyperlink usage and HyperlinkOf UDF CELL
  • Excel Functions = XL f(x)s XLfxs

Copyright © 2026 Excel Functions.

Powered by PressBook News Dark theme