Data Validation mystery

R

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.
 
B

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)
 
R

Ron@Buy

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top