Not much time now to reply, but in the past I have used dynamic named
ranges for this. You define the controls with a named range, and you
define the named range dynamically so that what it calls up is a
function of what another control returns.
This may not help much without the workbook to reference, but here is
an example of how I named such a range. Maybe someone else here can
explain more clearly.
=OFFSET(Constants!$H$3,MATCH(Variables!$D$30,Const ants!$H:$H,0)-3,1,COUNTIF(Constants!$H:$H,Variables!$D$30),1)
Basically, the list of ddm1 options was:
Residential
Commercial
Industrial
Government
Fire Fighting
Non-Account
Agriculture
so I set up the list of DDM2 options with two columns (one flag and one
data) like
Residential Single
Residential Multi
Commercial 01 - 09
Commercial 15 - 17
Commercial 40 - 48
Commercial 50 - 51
Commercial 52 - 59
Commercial 60 - 67
Commercial 70 - 89
Industrial 14
Industrial 20, 22 - 39
Industrial 49
Government 91 - 97
Fire Fighting NA
Non-Account NA
Agriculture NA
to use the match between what DDM1 returns with the rows I'd like DDM 2
to pull up. Sorry if this is not clear ... Friday 4:53 and all.
btw ... this answer is clearly not trivial, but using these concepts is
very powerful and worth the time to work it through.
--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile:
http://www.excelforum.com/member.php...fo&userid=1256
View this thread:
http://www.excelforum.com/showthread...hreadid=382102