On Fri, 19 Nov 2010 06:11:57 -0800 (PST), Don Guillett Excel MVP
wrote:


I'm a bit lost at what you are trying to do but:
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

I fill a "form" with a list of actor names that are resolved by looking
at four worksheets that are laid out like a database table.

Since there is more than one actor for a film, the table has to look up
all the actor numbers in the actor index worksheet (actor numbers, and
film numbers, and then go grab the names that that list of numbers
correspond to in the actors worksheet. This keeps from repeating actor
names in full length in the index worksheet. The number takes up far less
space, and the actors only need be listed once.

So, I grab the Director name from two similar tables that appears fine
in another cell as most films are directed by a singular person (I know,
error) in another cell in my "main" worksheet, which is a sheet that has
lookups of all the data for a given film in the "listing" worksheet.

That way I see a single set of data for one film, even though the
worksheet that has all the 'fields' for several hundred thousand films.

Sorry, but it is a 40MB workbook, and you likely do not need it to help
me. So, I will give you this shot of my "main page" Which may further
your comprehension of my workbook structure.

It is MOSTLY flat file, single workbook, however, as we all know
there is no need to redundantly name the director over and over again in
the main file when the film number and director name can be the only
'list', or 'table' needed, hence the "Diectors Index", and Director
Names" worksheets. The actors are typically plural in a film, and the
single flat file is not conducive to large blocks of allocated 'open
space' to make way for the actor name(s)<problem.

SO, there is an "Acted_In" worksheet and an "Actors" worksheet.

The index is as long as the film list, several hundred thousand
entries.

The actors that match up with that cross reference only need be listed
once.

So, with this perfect homemade database engine in place, I can track
300,000 DVDs, Blu Ray, HD DVD, etc by simply showing one listing on a
form like sheet, and having the director and actorS<< looked up.

This script *should* perform the multiple actor list lookup and fill it
into a table or range nearby on the main ws, Which I then DO have a list
of empty cells that only show data when the formula resolves an actual
name from the nearby table that was populated. It should populate
a small table on my main sheet, which then fills by having a live lookup
on those cells (the filled table)

Unless there is some easy way to show matched "records" in a worksheet
that are plural and a subsequent (and different) worksheet lookup from
the initial match.

Here is my 'view form' or whatever you folks call it.

Also, as a side note, this is faster than your Access database app. It
is miserable at lookups (speed wise)in record arrays this large. Excel is
nearly instantaneous.

Here is a screen shot:

http://www.mediafire.com/i/?u59urez0w7ta23u

The "Actors" banner is what one clicks to initiate the lookup script.

Table4 is to the right of the area where the name array gets filled in,
and those columns are usually 'off screen'. The other tables are in
their corresponding worksheets.

There are 'buttons' for me to DL new database updates, and to convert
and import them. They come from ASCII files.

Used to work great, so I fear the problem is after the import of new
records. My conversion scripts have not changed though, so it must be
some array declaration and subsequent mismatch.