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" <[email protected]> wrote in message
> news:[email protected]...
>
>>< 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.
Bookmarks