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

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

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

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

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

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

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

Biff is right (as usual), sorting the data can be the biggest improvement
that you can make. I would certainly expect to see better from that than
grouping dependent cells.

Also, VLOOKUP can be a killer on large tables, so alternatives can often
speed things up.

--
HTH

Bob Phillips

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

"Biff" wrote in message
...
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

Thank you all for your help. I apologise for the delay in responding.
I think I'm going to have to look at ALL manner of things to see if I
can hustle things up. Thanks again,
Don
Bob Phillips wrote:
Biff is right (as usual), sorting the data can be the biggest improvement
that you can make. I would certainly expect to see better from that than
grouping dependent cells.

Also, VLOOKUP can be a killer on large tables, so alternatives can often
speed things up.

--
HTH

Bob Phillips

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

"Biff" wrote in message
...
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

Probably the best resource of its kind. It's all about optimization:

http://www.decisionmodels.com/

Biff

"Don" wrote in message
ups.com...
Thank you all for your help. I apologise for the delay in responding.
I think I'm going to have to look at ALL manner of things to see if I
can hustle things up. Thanks again,
Don
Bob Phillips wrote:
Biff is right (as usual), sorting the data can be the biggest improvement
that you can make. I would certainly expect to see better from that than
grouping dependent cells.

Also, VLOOKUP can be a killer on large tables, so alternatives can often
speed things up.

--
HTH

Bob Phillips

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

"Biff" wrote in message
...
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