Problems with PULL function, INDIRECT.EXT and so forth
 

Hi

Off to a bad start, my first post seems to have disappeared so her goes
again......

I am trying to put together a spreadsheet which collects summary data
from around 50 other workbooks [in case it is important I am running
excel 2002 SP3 on windows xp].

My first port of call was the indirect command, but this obviously only
works on open books. This lead me to indirect.ext in the morefunc
add-in. Sadly this just froze excel every time I typed in the command.
I then tried indirect2 the sequel by the same author, but this only
worked very occassionally.

I then came across the PULL function by Harlan Grove, which seemed to
be just the ticket, but sadly this only ever returns the #value error.
I have tried this in the most simple of scenarios (two workbooks, one
with numbers and the other with the pull command) but this also failed.


Can anyone offer any suggestions or am I doomed by a compatibility
issue and the high chance of leaping from the 7th floor window!

Many thanks
Steve

Hi steve

I reply with this

Why don't you use links ?

Maybe this will help
http://www.rondebruin.nl/summary2.htm

Other examples are on my tips page
http://www.rondebruin.nl/tips.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message oups.com...
Hi

Off to a bad start, my first post seems to have disappeared so her goes
again......

I am trying to put together a spreadsheet which collects summary data
from around 50 other workbooks [in case it is important I am running
excel 2002 SP3 on windows xp].

My first port of call was the indirect command, but this obviously only
works on open books. This lead me to indirect.ext in the morefunc
add-in. Sadly this just froze excel every time I typed in the command.
I then tried indirect2 the sequel by the same author, but this only
worked very occassionally.

I then came across the PULL function by Harlan Grove, which seemed to
be just the ticket, but sadly this only ever returns the #value error.
I have tried this in the most simple of scenarios (two workbooks, one
with numbers and the other with the pull command) but this also failed.


Can anyone offer any suggestions or am I doomed by a compatibility
issue and the high chance of leaping from the 7th floor window!

Many thanks
Steve

Ron de Bruin wrote...
Hi steve

I reply with this

Why don't you use links ?

Maybe this will help
http://www.rondebruin.nl/summary2.htm
....

Fair question if OP is just pulling summary data that would become
effectively static/archived data.

However, macros are unnecessary in general, and yours requires
re-editing on every use unless all parameters are exactly the same. And
it only handles a single worksheet in each file.

Easier to construct links entirely in worksheets. For example, if
there's a full directory path in a cell named PATH, a list of workbooks
within that directory in a range named WBList, a list of common
worksheet names in a range named WSList, and a list of addresses for
ranges to sum in each of these worksheets in a range named RAList, then
create the needed formulas starting in A1 of another worksheet first by
creating string formulas that evaluate to the needed formulas.

A1:
="=SUM('"&Path&"\["
&INDEX(WBList,1+INT((ROWS(A$1:A1)-1)/(ROWS(WSList)*ROWS(RAList))))
&"]"&INDEX(WSList,1+MOD(INT((ROWS(A$1:A1)-1)/ROWS(RAList)),ROWS(WSList)))
&"'!"&INDEX(RAList,1+MOD(ROWS(A$1:A1)-1,ROWS(RAList)))&")"

Fill A1 down until it returns #REF! errors, copy the non-error values
in col A, paste special as values into another column, then with the
pasted range still selected run Edit Replace and replace = with =.

This is easily adapted for multiple directories, different or multiple
function calls, etc.

As for my PULL function, the latest version is at

ftp://members.aol.com/hrlngrv/pull.zip

It works for me, but it's not the best approach for this.

Hi Harlan

Thanks for your reply (always useful)
The OP have things to do<g

I will check out the latest version of Pull this weekend.


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Harlan Grove" wrote in message oups.com...
Ron de Bruin wrote...
Hi steve

I reply with this

Why don't you use links ?

Maybe this will help
http://www.rondebruin.nl/summary2.htm
...

Fair question if OP is just pulling summary data that would become
effectively static/archived data.

However, macros are unnecessary in general, and yours requires
re-editing on every use unless all parameters are exactly the same. And
it only handles a single worksheet in each file.

Easier to construct links entirely in worksheets. For example, if
there's a full directory path in a cell named PATH, a list of workbooks
within that directory in a range named WBList, a list of common
worksheet names in a range named WSList, and a list of addresses for
ranges to sum in each of these worksheets in a range named RAList, then
create the needed formulas starting in A1 of another worksheet first by
creating string formulas that evaluate to the needed formulas.

A1:
="=SUM('"&Path&"\["
&INDEX(WBList,1+INT((ROWS(A$1:A1)-1)/(ROWS(WSList)*ROWS(RAList))))
&"]"&INDEX(WSList,1+MOD(INT((ROWS(A$1:A1)-1)/ROWS(RAList)),ROWS(WSList)))
&"'!"&INDEX(RAList,1+MOD(ROWS(A$1:A1)-1,ROWS(RAList)))&")"

Fill A1 down until it returns #REF! errors, copy the non-error values
in col A, paste special as values into another column, then with the
pasted range still selected run Edit Replace and replace = with =.

This is easily adapted for multiple directories, different or multiple
function calls, etc.

As for my PULL function, the latest version is at

ftp://members.aol.com/hrlngrv/pull.zip

It works for me, but it's not the best approach for this.

Great, thanks for your help guys.