May be back with a couple more what-if's, the code looks pretty
intense! But at least I know what it is supposed to do, which will
be helpful.

I was already thinking how to handle if the lists get edited after
they're stored in the array. If shortened the resize to ClearContents
will leave populated cells. My thought is to store a delimited list...

name:rows

...and use that instead of current size!

--
Garry

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

Ok.., I revised as explained so take another look!

--
Garry

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

Hi Garry,

I was having a problem with adding the third drop down and columns in a earlier post, where you added it for me and reposted to the link.

I was looking for what I was missing in the process, wanting to learn how to do
it myself.

So I am trying to do a fourth drop down and I believe I have everything in place except the DD_4_16 name (for column 19, which is "S") where the Refers To: window should have the formula like this. At least I think so as I look at the others for an example.

=OFFSET(Sheet2!Hdr4_19,1,0,COUNTA(Sheet2!Col4_19)-1,1)

Whenever I type (not copy) that formula in the window I get this:

=OFFSET(Sheet2!Hrd4_19,1,0,COUNTA('Drop Down List Copied Garry_v2 (1).xlsm'!Col4_19)-1,1)

And the drop down does not produce the list on sheet 1.

If you get some time could you A, B, C me through the steps to do column S to work in the fourth drop down. I assume you have to do each column separate..?

I have the drop down and it is included in the DD_Lists, as cell H2 and DD4_Lists includes DD_4_16. If I click on DD4_Lists in the formula window, cell S1 on sheet 2 has the marching-ants on it.

So in plain third grade Canadian/English can you list the steps please. Probably should include the entire process even though I may have some already done a few of the correct steps.

Methinks I got all my DD_ this and that's correct, typos can be very misleading.<g

Thanks.
Howard

I have no idea why you're getting that ref after entering the formula!

First thing I should mention is the list numbering scheme you're using
doesn't sit well with me! I prefer to persist the same logic in terms
of refs to each list...

For DD2_List I'd go with..
DD_2_1. DD_2_2, DD_2_3, DD_2_4, DD_2_5
..so they clearly ref list 2, and their respective index.

...but I didn't bring this up due to the other concerns being prcessed.
That means I'd rather see your DD_4_16 named DD_4_1, and its column
isn't relevent to anything other than sheet layout. I revised my copy
to reflect this change so have another look!

Also, as you've seen, I've been inserting a narrow column between the
list groups and using alternating fill colors for odd/even blocks. That
means the DD_4 block would be the same fill as DD_2 block. (I just
don't feel more colors are needed to visually distinguish the groups!)


The step-by-step you requested:
<Using the NameBox
01: Name each hdr in same fashion as existing hdrs;
'Select each hdr cell before naming
<Example
Select DD_4_1;
click in the NameBox and type Sheet2!Hdr4_1;
Press the Enter key;
Right arrow to DD_4_2 and repeat.

02: Name the hdr group in the same fashion as existing groups;
Select all the DD4 hdrs;
Click in the NameBox and type DD4_Lists
Press the Enter key;

03: Name list cols in the same fashion as existing list cols;
'Select each DD4 list col before naming
<Example
Select DD_4_1;
click in the NameBox and type Sheet2!Col4_1;
Press the Enter key;


<In NameManager
04: Name each DD_4 list in same fashion as existing dynamic lists;
'If 'Sheet2' is the active sheet you won't need to specify
'the sheetname while typing the formula!
<Example
Type DD_4_1 in the Name box;
Tab into the RefersTo box and type...
=offset(hdr4_1,1,0,counta(col4_1)-1,1)
Add the name and repeat for the others in the group.

05: Update DD_Lists.RefersTo to include the absolute address of DD4;

<On Sheet1
06: Update the DV List ref for DD4.

I didn't add anything to the revised file so you could try adding DD4
on your own.

--
Garry

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

Also, as you've seen, I've been inserting a narrow column between the
list groups and using alternating fill colors for odd/even blocks. That
means the DD_4 block would be the same fill as DD_2 block. (I just
don't feel more colors are needed to visually distinguish the groups!)


The step-by-step you requested:
<Using the NameBox
01: Name each hdr in same fashion as existing hdrs;
'Select each hdr cell before naming
<Example
Select DD_4_1;
click in the NameBox and type Sheet2!Hdr4_1;
Press the Enter key;
Right arrow to DD_4_2 and repeat.

02: Name the hdr group in the same fashion as existing groups;
Select all the DD4 hdrs;
Click in the NameBox and type DD4_Lists
Press the Enter key;

03: Name list cols in the same fashion as existing list cols;
'Select each DD4 list col before naming
<Example
Select DD_4_1;
click in the NameBox and type Sheet2!Col4_1;
Press the Enter key;


<In NameManager
04: Name each DD_4 list in same fashion as existing dynamic lists;
'If 'Sheet2' is the active sheet you won't need to specify
'the sheetname while typing the formula!
<Example
Type DD_4_1 in the Name box;
Tab into the RefersTo box and type...
=offset(hdr4_1,1,0,counta(col4_1)-1,1)
Add the name and repeat for the others in the group.

05: Update DD_Lists.RefersTo to include the absolute address of DD4;

<On Sheet1
06: Update the DV List ref for DD4.

I didn't add anything to the revised file so you could try adding DD4
on your own.

--
Garry

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

Got the new sheet and these instructions.

Off to try it.

Thanks, again!

Howard

Got the new sheet and these instructions.

Off to try it.

Thanks, again!

Howard

And we have lift off!!

https://www.dropbox.com/s/ozm7b21hln...20it.xlsm?dl=0

Thanks for all the help.

Will keep for reference, however the code is magic stuff to me.

Howard

Got the new sheet and these instructions.

Off to try it.

Thanks, again!

Howard

And we have lift off!!

https://www.dropbox.com/s/ozm7b21hln...20it.xlsm?dl=0

Thanks for all the help.

Will keep for reference, however the code is magic stuff to me.

Howard

Well not exactly! Your DD4 names are global instead of local. (I
suspect you did not prefix with the sheetname where instructed) Have a
look at my version in the NameManager window and note the 'Scope' for
each name. Then fix your file so your names are as per the
instructions...

"..same as the other..."

...and then consider it a passing grade if it all works.

Note that the names are all local scope except for the dynamic ranges
and DD#_Lists. You can fix the RefersTo only in Excel. You can change
the scope in the NameManager addin using multi-select, but to do this
in Excel you have to delete the global names 1st, then recreate them as
local. I reiterate that you start using the NameManager addin!!!

--
Garry

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