Here's how I use this methodology...
1st DV col lists Chart of Accounts (CoA);
-this is a vertical dynamic named range in ColA of a sheet named
"Lists"
Running horizontally off each item in the CoA are sub-accounts. Each
item, then, is also the name of a horizontal dynamic range.
Now, the CoA is divided into 3 main sections; Income, CostOfSales, and
Expenses. The Income list contains sales categories. The Expenses list
is further divided into OperatingExpenses and OtherExpenses.
So to give example...
A | B | C | D | E |...and so on
Income | Revenue1 | Revenue2 | Revenue3 | Income:Other
Cost Of Sales | Freight In | Freight Out | Delivery Expense | COS:Other
Expenses
Administration | Admin Fees | Management Fees | Admin:Other
Insurance | Building | Liability | Insurance:Other
Taxes | Business | Property | Taxes:Other
Bank Charges | Fees | Interest | Bank Charges:Other
OtherExpenses
Web And Internet | Admin Charges | Internet Service | Web:Other
Vehicle Expense | Repairs | Insurance | Fuel/Oil : Vehicle:Other
...where each named range is contiguous. Names are the CoA list minus
spaces so I can use the following formula in the sub-account DV...
=INDEX(SUBSTITUTE(ExpenseCategory," ",""))
...so when I select 'Bank Charges' on the Expenses sheet its dependent
DV lists the 3 items in the dynamic range named "BankCharges". The 1st
DV is in a column-absolute, row-relative range named "ExpenseCategory"
on the Expenses sheet. Its DV formula is "=ExpenseCategories" as that's
the name of the Expenses section of the CoA.
Suggestion:
List the manufacturers in colA, and list models for each manufacturer
horizontally as exampled above.
Name colA dynamic "Makes".
Name the models lists the manufacturer.
In your sheet 1st DV "=Makes".
In your 2nd DV "=INDEX(SUBSTITUTE(Make," ",""))", and name that col
"Make" so it's row-relative.
Note that these DV dynamic names need to have workbook level scope, BUT
make all sheet named ranges local scope (sheet level)!
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion