Look Who it Was Random video

Excel - Harlan Grove's PULL Function is Like INDIRECT for External Workbook - Episode 820

4,732 views · Published 12 January 2009 · 2:42 · Indexed 22 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Harlan Grove's PULL Function is Like INDIRECT for External Workbook.

Welcome back to the MrExcel netcast! In this video, we're going to tackle a common problem faced by many Excel users - how to retrieve data from multiple workbooks. Specifically, we'll be looking at Keith's question, where he needs to use a VLOOKUP to grab values from different workbooks that are created daily.

In our previous netcast, we explored the use of the INDIRECT function to retrieve data from various worksheets within the same workbook. However, this solution won't work for Keith's situation as his data is saved in separate workbooks for each day. And to make matters more challenging, these workbooks are often closed.

But fear not, because we have a solution! Thanks to the brilliant work of Harlan Grove, we can use a user-defined function called PULL to retrieve data from closed workbooks. You can easily find this function by searching for "HARLAN GROVE PULL" on Google. Simply copy and paste the code into Excel and you're ready to go.

So how does the PULL function work? It's quite simple. We first build the file name using the CONCATENATE function, then specify the worksheet name and range we want to look up. Finally, we use the PULL function as the second argument in our VLOOKUP formula. This will open the specified workbook, retrieve the data, and return it to Excel. Keep in mind that this function may be a bit slow if you're retrieving a large amount of data, but it's a great solution for smaller data sets.

I hope this video was helpful in solving your workbook retrieval woes. Don't forget to subscribe to our channel for more Excel tips and tricks. Thanks for watching and we'll see you in the next netcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Building on yesterday's podcast, life becomes considerably more complex if you need to use VLOOKUP to look into a different workbook based on a date. Episode 820 will show you why INDIRECT won't work and takes a look at how to use Harlan Grove's PULL function. Download the function from ftp://members.aol.com/hrlngrv/pull.zip

Table of Contents:
(00:00) Introduction to the topic
(00:18) Using INDIRECT to grab values from various worksheets
(00:28) Limitations of INDIRECT
(00:38) Introducing Harlan Grove's PULL function
(01:10) How to access the PULL function
(01:20) Setting up the PULL function
(01:48) Using PULL as the second argument in VLOOKUP
(02:03) Recap and limitations of PULL function
(02:19) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Benefits and limitations of using Harlan Grove's PULL function in Excel
Conclusion and next steps for further learning on Excel data retrieval techniques
Exploring alternative methods for referencing data in closed workbooks in Excel
How to grab values from various worksheets in Excel using INDIRECT
How to use the PULL function to retrieve data from closed workbooks in Excel
Introduction to Harlan Grove's PULL function in Excel
Real-life example of using the PULL function for data retrieval in Excel
Solving the problem of referencing data in multiple workbooks in Excel
Step-by-step guide on setting up the PULL function in Excel
Tips for optimizing the performance of the PULL function in Excel
Using INDIRECT to reference closed workbooks in Excel
YouTube video on using INDIRECT and VLOOKUP in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152049/

More from this channel