Since you are on Excel 2003, there is no need for dynamic named ranges.
Convert all table areas into a LIST by means of Data|List|Create List.
If you can sort them in ascending order and maintain them in ascending
order, do so.

Lets A1:D200 house such a table with headers (fields) in A1:D1...

Turn A1:D200 into a LIST.
Select A2:D10, go to the Name Box on the Formula Bar, type TABLE, and
hit enter.

Any appropriate cell that you want to data validate, invoke:

=INDEX(TABLE,0,1)

in the Source box in order to have the items in A2:A100 as a list in
that cell.

If F2 is a data validated cell, you can invoke in, say, G2 a lookup
formula like...

(a) If TABLE is in ascending order, then:

=VLOOKUP(F2,Table,2,1)

(b) If TABLE is not sorted, then:

=VLOOKUP(F2,Table,2,0)

The one in (a) is quite faster

Tosca wrote:
Hi Niek

Yes, this works fine. Within each table for the lookup, there will be upto
30 rows of data, not several hundred, so I don't think that the speed will
be an issue. There will, however, be many (perhaps 200+) separate tables as
named ranges and I doubt that the VLOOKUP will be slow in this case as it is
looking at a specific named range of upto 30 rows, rather than looking at
each of the tables in sequence and then down each of the rows to find the
data. Is this argument logical? If so, I'm happy, otherwise I may have to
consider some other solution. The data *will* be found by VLOOKUP as I'm
using the first column for data validation when I'm entering the data in the
first place.

Thanks again for your time.

"Niek Otten" wrote in message
...

< I think because it passes the point in the list it expects to find the
answer

No. With th 4th argument set to FALSE, the list doesn't have to be sorted,
but is read sequentially from beginning to end (if the enry can't be
found). One consequence is that such a search is considerably slower,
which you can notice if you have hundreds or thousands of such VLOOKUPs.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel




--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.