Hi Garry,

Am Thu, 22 May 2014 12:24:55 -0400 schrieb GS:

Not true! My Invoicing: Simple Bookkeeping app uses dynamic ranges
on a hidden sheet for its ChartOfAccounts used by the dependant DVs
on its 'Income' and 'Expenses' sheets, as well as its 'Summary'
(profit/loss) sheet. This was deliberate so any changes in the COA
at runtime would automatically reflect in the DVs. Ref to the
ranges includes the hidden sheet's name.

I never get a depending DV with INDIRECT to work, if the range is
defined by OFFSET formula.

From: http://www.contextures.com/xlDataVal02.html:

<Using Dynamic Lists

Because the INDIRECT function only works with references, not
formulas, the previous method for dependent data validation won't
work with dynamic lists.<

This DV formula...

Expense Sub-Category input field DV List ref:
=INDIRECT(SUBSTITUTE(ExpenseCategory," ",""))

...uses a local scope defined name (ExpenseCategory) that's
column-absolute, row-relative. This is the dependant DV.

The controlling DV is the one dependant on the dynamic range named
"ExpenseCategories", which is global scope.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion