Data Validation mystery

[Ron@Buy]

Using Excel2003 with Windows xp
One Workbook - Two worksheets

Two columns in worksheet1 - Data Validation in cells of both columns
Selecting a word from the drop down list in column 1 allows selection of a
word from a drop down list in column 2 (changing the word in column 1 offers
a different drop down list of words in column 2).

Worksheet2 has 4 columns of words, each column with a Heading
Column 1 has its own Heading and contains the Heading of columns 2, 3 & 4
Columns 2, 3 & 4 each contain a different list of words
Named ranges: Each column Heading is 'Named' using the column Heading.
The words in each column, below each Heading, are range 'Named' with the
column Heading with 'list' tagged on the end,
e.g. Column 1 Heading is 'Designation' and "Named" 'Designation'. The list
of words below the Heading in Column 1 are range "Named" 'Designationlist'.
Columns 2, 3 & 4 Headings and list of words are similarly "Named".

Worksheet1 - Column 1, cells contain Data Validation - List, Source:
=Designation (ie "name of Heading of Column 1 Worksheet2")
Worksheet1 - Column 2, cells contain Data Validation - List, Source:
=OFFSET(INDIRECT(SUBSTITUTE($A2," ","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($A2,"
","")&"list")),1)

This works fine, selection of word in Column 1 causes a list of words to be
selected in Column 2. However, the list of words in Column 2 (Worksheet 1)
will only ever display words from Columns 2, 3 & 4 (Worksheet2) down to Row
27!

Anybody any idea why the drop down list aborts a Row 27 ?

Any help / suggestions would be appreciated.

 

[Bob Phillips]

As far as I can see it always drops the last and includes the heading. Try

=OFFSET(INDIRECT(SUBSTITUTE($A2,"
","")),1,0,COUNTA(INDIRECT(SUBSTITUTE($A2," ","")&"list")),1)

 

[Ron@Buy]

Bob
Clearly a case of 'can't see the woods for the trees!'
Many thanks - perfect solution