Just to add to Bob's reply:

If you use Vlookups on a "huge" table you can significantly speedup the
lookup by sorting the lookup table. That's not always possible but it can
really help.

Biff

"Bob Phillips" wrote in message
...
Epinn,

I wasn't referring to Don's original question, but a totally fictitious
situation. So my D1 was not intended to be part of Don's array.

As for all of those 'rules' that you quote, I personally don't give them
much credence. IMO the first thing to do is to deign a good spreadsheet,
by
laying out the data well, it will make it far easier to extract the
information, and you can reduce the amount of calculating that is going
on.
If and when you have a problem with performance, starting to look at where
the bottlenecks are, and look to improve those areas. The solution might
be
moving some data around, reducing array formulae, dynamic ranges, UDFs,
all
manner of things. Unfortunately, there is no simple, easy answer, that is
why you pay Excel consultants so much money :-)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob,

I also want to learn. I think I understand the concept, but as always I
am
having a hard time interpreting formulae. Before I pull any more hair,
can
you help me understand one thing first if you don't mind.

consider a formula in say D1 that checks if a
VLOOKUP operation is successful or not

I am kind of lost here because D1 is part of the table/array of A1:D20.

Am I missing something here?

Once I understand this, I'll try to figure out the rest like:

and then in D1 use

=IF(ISNA(D1),"",D1)

ISNA(D1) or ISNA(E1)

Yesterday I learned that for complex worksheets, primary calculations
should
be placed near the top of the worksheet and as far left as possible, and
the
calculations that are based on those primary calculations are placed later
in the worksheet. I am not sure how to apply this "rule" to the location
of
a table (especially a huge one) used by VLOOKUP. Sometimes the table is
in
another workbook. Will things speed up if we include the table in the
same
worksheet? Is it even better if we place the table closer to the VLOOKUP
formula to speed things up? But then the VLOOKUP formula may be a
*primary*
formula and I certainly don't want a huge table close to the top left of
the
worksheet.

Sorry Don, if you think I have gone off-track.

Bob, appreciate your wisdom.

Epinn

"Bob Phillips" wrote in message
...
As a very simple example, consider a formula in say D1 that checks if a
VLOOKUP operation is successful or not

=IF(ISNA(VLOOKUP("Bob",A1:D20,2,FALSE)),"",VLOOKUP ("Bob",A1:D20,2,FALSE))

If you have the following the following in E1

=VLOOKUP("Bob",A1:D20,2,FALSE)

and then in D1 use

=IF(ISNA(D1),"",D1)

the VLOOKUP, which checks many cells is only invoked once, and then a much
shorter test is made in D1.

That is the basic technique, but clearly it can be adapted many ways.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Don" wrote in message
oups.com...
Could you give me an example of this?
Thanks,
Don
Bob Phillips wrote:
That could be one way to help. Others might be in having intermediate
calculations in helper cells, so that fewer cells are checked for
recalc
each time.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Don" wrote in message
ps.com...
Hello,
In a nutshell: I am putting together a database. I created a search
"grid" that allows the user to choose a name from a data validation
list. After selecting a name, the grid (shows 4 locations across,
and
the 12 months down) uses Sumproduct to count the number of units the
selected name has for each site, in each month.

The problem: I would like to use this database over a few years.
Each
year I will be adding almost a thousand entries. I have been using a
set range, eg D7:D10000, for Sumproduct to search. I have an old
workstation at work, and after each entry it can literally take 2-3
full seconds for the workbook to re-calculate. It has been suggested
that I use a dynamic named range using
=OFFSET(Data!G7,,,CountA(Data!$B:$B),1), instead of the range I
indicated above. This would allow the formulas to only look in the
actual range, instead of a bunch of blank cells.

I haven't been able to put this to a try yet. So my question is---am
I
on the right track? Is there a different technique I should be
considerin?

I hope I have explained this clearly enough. Please let me know if I
can supply any other need info.

Thanks,
Don