INDIRECT Reference to Named Range in another Workbook

[indigowave]

I have had to break apart a workbook due to size limitations. It's spreadsheet of baseball statistics, so the I have a named range for each pitcher, and within the range are rows for each performance. On my main sheet, I use a VLOOKUP(todays' date, INDIRECT(pitcher name), 30, true) formula to pull the previous pitching performance. When I moved the sheet of named ranges to its own workbook, I now get #REF errors. Is there a way around this?

Thanks,

Jeff

 

[Zack Barresse]

=vlookup(today(),book1.xls!PitcherName,30,1)

HTH

 

[indigowave]

The problem is that I don't want to type the pitcher name in every time. So on the main sheet, say A1 is today's date and B1 is pitcher name, my formula is vlookup(A1,indirect(b1),30,true) to pull the data through the previous day. I just can't get the INDIRECT portion to pull from another workbook.

Jeff

 

[Ekim]

Jeff,

Indirect does not work with a closed workbook – “If the source workbook is not open, INDIRECT returns the #REF! error value” (per Excel’s Help File).

The following threads deal with getting data from closed workbooks (there are several other threads on the same topic - use this Board's Search facility). Hopefully, you will find a solution that fits your requirements.

http://www.mrexcel.com/board2/viewtopic.php?t=67212
http://www.mrexcel.com/board2/viewtopic.php?t=65837&highlight=
http://www.mrexcel.com/board2/viewtopic.php?t=70457&highlight=

HTH

Mike

 

[just_jon]

See the reference to the PULL UDF by Harlan Grove in this post.

 

[Zack Barresse]

try...

=vlookup(today(),indirect("book1.xls!"&PitcherNameCell),30,1)

 

[Ekim]

Jon,

In my post above, I refer to Harlan Grove’s “Pull” function in this thread: http://www.mrexcel.com/board2/viewtopic.php?t=70457&highlight=

I also made this warning in the same thread:
Do not try to drag Mr. Grove’s UDF formula down or across the page. On my PC, Excel “freezes” and requires Ctrl-Alt-Delete to get out of it. Similarly, in one application I have a validation dropdown that houses file names of closed workbooks. If I selected a file name that does not exist, the UDF went into an endless loop. In this case, only a hard re-boot worked (Ctrl-Alt-Delete failed).

That said, Mr. Grove’s UDF works very well to extract data from a closed workbook.

Regards,

Mike

 

[just_jon]

Thanks, have fixed the ref Mike -- good info.

 

[Zack Barresse]

for a graphic representation of my solution...

Book5
	E	F	G	H
1	Date	Pitcher'sName	FindValue
2	3/26/2004	Thumper	0.405
3
4	Datainbook6lookslikethis:
5	3/26/2004	0.405
6	3/25/2004	3.20
Sheet1

works well for me.

formula is:
=VLOOKUP(E2,INDIRECT("Book6.XLS!"&F2),2,0)

HTH

 

[jmartell]

is there anyone online who is =INDIRECT savvy? I am working on a project and need desperate help.