vlookup usage to another workbook
Pacific
VLOOKUP to access names, addresses etc.
At this stage, my 'names' are defined in the 'client' master w/book and
VLOOKUPs refer to data as 'client.xls!client_lup' for example.
How does this compare to defining the name in the target book and then
simply referring in the VLOOKUP to 'client_lup'. Is there any difference in
performance?
I ask mainly because all the target books seem to take a long time to load
(they range in size from approx 100k to 600k). Thinking on the fly - I
suppose it would be sensible to copy/paste values as the data becomes
historical anyway??
Because I'm not sure how to define a dynamic range, my names are always
defined with plenty of spare room - the full lookup range is A1:M500. Is
this what's costing me?
While we're at it, I have the same problem adding all the columns within a
sheet. I always have totals at top for display and will sum say 4:500 for
each column. Again, does this cost performance or is it easy enough to set
dynamic ranges?
TIA to all those wizards for whom this might be trifling.
Ian.
Harlan Grove
...
>At this stage, my 'names' are defined in the 'client' master w/book and
>VLOOKUPs refer to data as 'client.xls!client_lup' for example.
>How does this compare to defining the name in the target book and then
>simply referring in the VLOOKUP to 'client_lup'. Is there any difference in
>performance?
There may be some, especially if client.xls isn't open.
>I ask mainly because all the target books seem to take a long time to load
>(they range in size from approx 100k to 600k). Thinking on the fly - I
>suppose it would be sensible to copy/paste values as the data becomes
>historical anyway??
These aren't 'large' by current standards (well, if attached to a newsgroup
posting they be way too large, but that's a different matter). How new/fast
is your PC? How fragmented is your harddisk?
>Because I'm not sure how to define a dynamic range, my names are always
>defined with plenty of spare room - the full lookup range is A1:M500. Is
>this what's costing me?
Maybe. If you're not using False as the optional 4th argument to VLOOKUP,
VLOOKUP could get confused when it hits the rows with blank cells.
>While we're at it, I have the same problem adding all the columns within a
>sheet. I always have totals at top for display and will sum say 4:500 for
>each column. Again, does this cost performance or is it easy enough to set
>dynamic ranges?
There'd be some performance drag, but not much for SUM. It's not so much
adding 0 to the accumulator as it is accessing and dereferencing blank
cells. So, yes, dynamic ranges would help, but they're not cost-free. If you
define a dynamic range using OFFSET, I believe the defined name is recalc'ed
every time it's accessed in a worksheet function. Fastest is to define a
name referring to a cell that contains a formula that evaluates to the text
address of the dynamic range, and wrap the defined name inside INDIRECT.
This introduces an additional function call, so may not be worth the bother
if there are only a few formulas referring to the dynamic range.
Charles Williams
recalculations.
The main performance hit in a dynamic range formula is the COUNTA function,
because this has to scan a large number of rows.
So the fast solution is to put the COUNTA formula in a separate cell and
then refer to it in the Dynamic Range name.
hth
Charles Williams
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com
"Harlan Grove" <hrl...@aol.com> wrote in message
news:#bLHDezcBHA.2212@tkmsftngp05...
Pacific
"Pacific" <ch...@mira.net> wrote in message
news:q40L7.525$is1....@nasal.pacific.net.au...
Aladin Akyurek
<hrl...@aol.com> wrote:
> "Pacific" <ch...@mira.net> wrote...
> <snip>
> >While we're at it, I have the same problem adding all the columns within a
> >sheet. I always have totals at top for display and will sum say 4:500 for
> >each column. Again, does this cost performance or is it easy enough to set
> >dynamic ranges?
>
> There'd be some performance drag, but not much for SUM. It's not so much
> adding 0 to the accumulator as it is accessing and dereferencing blank
> cells. So, yes, dynamic ranges would help, but they're not cost-free. If you
> define a dynamic range using OFFSET, I believe the defined name is recalc'ed
> every time it's accessed in a worksheet function.
Yeah.
> Fastest is to define a
> name referring to a cell that contains a formula that evaluates to the text
> address of the dynamic range, and wrap the defined name inside INDIRECT.
> This introduces an additional function call, so may not be worth the bother
> if there are only a few formulas referring to the dynamic range.
I've been exploring this option quite a while. I didn't attempt any timing
though because there wasn't any distinct performance gain in computing the
dynamic ranges locally in a worksheet as opposed to computing them
directly inside the Name Box. Recalcs kept occurring, alas.
Aladin Akyurek
creating named dynamic ranges is to exploit the existence of column of
numeric type. Suppose that A contains names, B contains birth dates or
salaries.
Define MaxRecs as =MATCH(9.99999999999999E+307,x!$B:$B)
Define Arange as =OFFSET(x!$A$1,0,0,MaxRecs,1)
Define Brange as =OFFSET(x!$B$1,0,0,MaxRecs,1)
Caveat. There should not be a number in the numeric column as big as
Excel's constant 9.99999999999999E+307, unless it is in the last used
cell.
Would this be something for DecisionModels to explore?
Aladin
In article <OjJJfx0cBHA.2104@tkmsftngp04>, "Charles Williams"
David G. Patton
could one use =MATCH(65536,B:B)
Would this be more efficient than Counta(B:B)?
Dave Patton
Aladin Akyurek wrote in message ...
Charles Williams
Thats an excellent and interesting idea.
Test results on my system using Excel2000 are
500 rows of numbers in a column:
COUNTA is about twice as fast as MATCH
13000 rows of numbers in a column
MATCH is faster, takes about 75% of the COUNTA time.
I guess the MATCH advantage would be larger on very long columns.
Aladin Akyurek
No, using 65536 (I guess you're referring to the max number of rows) would
be problematic (too common a number). Suppose that B1:B3 houses the
following salaries.
{65536;24597;0;123654}, where 0 stands for a blank cell.
[1] =MATCH(65536,B:B) would give us a record count of 2. Not a result we
are after.
[2] =MATCH(9.99999999999999E+307,B:B) would produce the desired result.
[3] =COUNTA(B:B)
will fail miserably to produce the correct result when the column of
interest is interspersed with blank cells, a point I skipped in my
previous post.
The formula [2] exploits the fact that MATCH failing to find a number
matching 9.99999999999999E+307 under match-type TRUE (or 1) defaults to
the location (row number) of the last cell housing a numeric value.In
article <uk0EXT4cBHA.2120@tkmsftngp07>, "David G. Patton"
Aladin Akyurek
Great. Thanks. I can now better motivate when to use it in creating named
dynamic ranges.
Aladin
In article <uTKfP74cBHA.1980@tkmsftngp05>, "Charles Williams"
David G. Patton
I posted my comment so I and possibly others would learn.
Dave Patton
Aladin Akyurek wrote in message ...