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