# PULL function for Excel 2000?



## travisc (May 17, 2006)

Anyone know where I can get a PULL function that works with newer Excel versions? Or maybe even remake the original PULL function so it works with newer versions?

Thanks,
Travis


----------



## Andrew Poulsom (May 17, 2006)

Here?

http://tinyurl.com/63xon


----------



## travisc (May 17, 2006)

Do I just right click a sheet in the workbook --> View Code --> Paste in there?


----------



## travisc (May 17, 2006)

I don't understand the guy's usage in that thread either...

>VLOOKUP($A$3,Personal.xls!pull($C$3),5,FALSE) 
>where C3 contains 
>'Q:\NHL\[After Purge Accounts List 040518.xls]ImportData'! 
>$A$1:$H$50 

I don't understand the "Personal.xls!pull($c$3)" part...

I'd understand it if it didn't have the "Personal.xls!" part in there.


----------



## Zack Barresse (May 17, 2006)

That is there if you store the PULL function in your Personal.xls file.  If you do, then you'll need that there.  If you keep it in the file you are calling it from, you do not need it.


----------



## travisc (May 17, 2006)

cool, thanks


----------



## travisc (May 17, 2006)

i put that code in the editor, and when I try it, it says "That name is not valid"... and it highlights the PULL function


----------



## Zack Barresse (May 17, 2006)

The PULL function needs to go into a Standard Module.  Is that where you put it?  It also matters what project (file) you have the module in.


----------



## travisc (May 17, 2006)

VB Editer --> Insert --> Module --> Pasted the code there


----------



## Zack Barresse (May 17, 2006)

But _where_ is the Module located?  In what project??


----------



## travisc (May 17, 2006)

Anyone know where I can get a PULL function that works with newer Excel versions? Or maybe even remake the original PULL function so it works with newer versions?

Thanks,
Travis


----------



## travisc (May 17, 2006)

VBAProject(2nd GL Rating.xls) --> Microsoft Excel Objects --> Modules --> pull


----------



## Zack Barresse (May 17, 2006)

And that is the file you are calling it from?


----------



## travisc (May 17, 2006)

yep... and I have it trying to call 2nd Shift Labor.xls


----------



## Zack Barresse (May 17, 2006)

Do you have another routine with the same name?  Check all modules and look in the right drop down box just above the code pane.


----------



## travisc (May 17, 2006)

It's the only module I have...


----------



## Zack Barresse (May 17, 2006)

Check this out: http://www.support.microsoft.com/kb/180277

See if that helps.


----------



## travisc (May 17, 2006)

now that i've closed out of the file and reopened it... it accepts PULL... but it doesn't pull the information... file open or not.


----------



## travisc (May 17, 2006)

```
=IF(ISNUMBER(VLOOKUP($A4,PULL("'\\BUNT5DCSRV1\storage\Butler\BM_MFG\Production Reports\2006 Production Reports\May 06''\Labor\[2nd Shift Labor.xls]"&DAY($U$2)&"'!$B$3:$C$86"),2,FALSE))=FALSE,"",VLOOKUP($A4,PULL("'\\BUNT5DCSRV1\storage\Butler\BM_MFG\Production Reports\2006 Production Reports\May 06''\Labor\[2nd Shift Labor.xls]"&DAY($U$2)&"'!$B$3:$C$86"),2,FALSE))
```

Doesn't work.


```
=IF(ISNUMBER(VLOOKUP($A4,INDIRECT("'\\BUNT5DCSRV1\storage\Butler\BM_MFG\Production Reports\2006 Production Reports\May 06''\Labor\[2nd Shift Labor.xls]"&DAY($U$2)&"'!$B$3:$C$86"),2,FALSE))=FALSE,"",VLOOKUP($A4,INDIRECT("'\\BUNT5DCSRV1\storage\Butler\BM_MFG\Production Reports\2006 Production Reports\May 06''\Labor\[2nd Shift Labor.xls]"&DAY($U$2)&"'!$B$3:$C$86"),2,FALSE))
```

Does, but not with the file closed.


----------



## Zack Barresse (May 17, 2006)

With INDIRECT, yes, the file must be open.  To use on a closed workbook you must use the INDIRECT.EXT function from Laurent Longre's morefunc.xll addin; although you've got about one good calc before it will error out.

Maybe you could try an alternative: http://vbaexpress.com/kb/getarticle.php?kb_id=286

They both use an old Excle4Macro method.


----------



## travisc (May 17, 2006)

I tried INDIRECT.EXT also, but it doesn't work with a range of cells...


----------



## travisc (May 17, 2006)

Anyone know where I can get a PULL function that works with newer Excel versions? Or maybe even remake the original PULL function so it works with newer versions?

Thanks,
Travis


----------



## travisc (May 17, 2006)

Got another idea here...


```
=IF(ISNUMBER(VLOOKUP($A4,'\\BUNT5DCSRV1\storage\Butler\BM_MFG\Production Reports\2006 Production Reports\May 06''\Labor\[2nd Shift Labor.xls]1'!$B$3:$C$86,2,FALSE))=FALSE,"",VLOOKUP($A4,'\\BUNT5DCSRV1\storage\Butler\BM_MFG\Production Reports\2006 Production Reports\May 06''\Labor\[2nd Shift Labor.xls]1'!$B$3:$C$86,2,FALSE))
```

I just eliminated the DAY(), and put the day in myself... now the problem I'm having is if that sheet doesn't exist... instead of just returning an #NA or other error... it won't let me enter the formula.


----------



## travisc (May 17, 2006)

I started changing all my formulas to this, then encountered the problem with a sheet not existing...

Each sheet represents a day of the month... and if it's a Sat/Sun, it doesn't have a sheet...


----------



## Zack Barresse (May 18, 2006)

So do these formulas have to update from the files?  Do you have Update remote references and Save external links checked in Tools | Options?


----------



## travisc (May 18, 2006)

yea both are checked.


----------



## Zack Barresse (May 18, 2006)

You know, I don't know if this is it or not, kind of a long shot.  But your cell which houses your text string to be looked at by the Pull() function, what exactly do you have in there?  And I mean copy/paste _exactly_ what you have from your formula bar - everything in there.


----------



## Zack Barresse (May 18, 2006)

Okay, what I'm thinking, is if you have something like this as your text from your formula bar ...


'C:\Your\Path\Here\[Book13.xls]Sheet1'!A1:C10

.. it will not work.  This is because Excel interpets the first single apostrophe ' sign as saying "this is text" and will not show it in the cell.  If you want it to show up, you'll need *two[/i] single apostrophe '' signs; not be be confused with the single quote " sign(s).

So the above would be ...

''C:\Your\Path\Here\[Book13.xls]Sheet1'!A1:C10

Try it out and let us know.*


----------

