Hi Garry,

Am Thu, 22 May 2014 12:51:31 -0400 schrieb GS:

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.

thank you. I will try it.


Regards
Claus B.

I should mention that the ExpenseCategories list is vertical as it's
part of the ChartOfAccounts list (also vertical). All Sub-Category
lists run horizontal off their respective 'parent' in the
ChartOfAccounts. Thus the use of SUBSTITUTE() to remove any spaces in
the CoA list since these are the defined names of the sub-list dynamic
ranges.

Also, all ranges named on the 'Lists' sheet are local scope. (Thus the
ref in the global RefersTo includes the sheetname!definedname)

So...

ColA:ColB:ColC:...
Chart of Accounts
Income Categories:Revenue1:Revenue2:Other Income:Discounts Given
Expense Categories
CoGS:Inventory:Discounts:Direct Wages:Contracts:Freight In:Freight
Out
Administration:Admin Fees:Management Fees:Administration: Other
Advertising:LiteratuBusiness Cards:Marketing:Advertising: Other
Bad Debts:Uncollectable Rec'ls:Bad Debts: Other
Delivery Expense:Courier Fees:Freight:Postage:Delivery Expense: Other
...

...where each row is a horizontal dynamic range defined using OFFSET
with the name of the value in ColA (minus spaces). These list in the DV
dependant on what 'Expense Category' is selected in its DV. (Changing
the parent DV after selecting in the dependant DV 'flags' the latter to
indicate the cell contents are not found in the 'parent' range)

--
Garry

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