Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
Great, thanks for your help guys.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data from closed workbooks (pull func, indirect.ext, etc ....) | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |