Linking to Worksheets named in cells

Steve123 · Apr 25th, 2002, 07:09 AM

I have a sheet listing workbooks in column A, what i want to do is extract data from the same cell in each workbook
ie
=[Book1.xls]Sheet1!$D$5
=[Book2.xls]Sheet1!$D$5

Is there a way to format the Sheet name cells so they can be added to that formula, an extract function or does it have to be done by VB ?

nb: there are 2000+ Worksbooks that i need to extract the data from an identical cell (D5)from

Cheers

Asala42 · Apr 25th, 2002, 08:02 AM

Howdy,

The formulation approach is use of the INDIRECT function. Since you already have the file and cell reference in A you would use : =INDIRECT(A2) for example - although you may need the full path of the file listed.

Sadly there's a nasty drawback to INDIRECT with filenames: the files need to be OPEN for the INDIRECT to recognize the link - might be a serious problem if your dealing with a large number of files.

Anyone have a VBA solution?

Adam

Steve123 · Apr 25th, 2002, 08:29 AM

Cheers Adam,
I'm trying to avoid opening all the workbooks though - once i've done that, i may as well cut n paste the value from D5 into the master file

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 07:09 AM	#1
Steve123 New Member Join Date: Apr 2002 Posts: 2	I have a sheet listing workbooks in column A, what i want to do is extract data from the same cell in each workbook ie =[Book1.xls]Sheet1!$D$5 =[Book2.xls]Sheet1!$D$5 Is there a way to format the Sheet name cells so they can be added to that formula, an extract function or does it have to be done by VB ? nb: there are 2000+ Worksbooks that i need to extract the data from an identical cell (D5)from Cheers

Apr 25th, 2002, 08:02 AM	#2
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,030	Howdy, The formulation approach is use of the INDIRECT function. Since you already have the file and cell reference in A you would use : =INDIRECT(A2) for example - although you may need the full path of the file listed. Sadly there's a nasty drawback to INDIRECT with filenames: the files need to be OPEN for the INDIRECT to recognize the link - might be a serious problem if your dealing with a large number of files. Anyone have a VBA solution? Adam

Apr 25th, 2002, 08:29 AM	#3
Steve123 New Member Join Date: Apr 2002 Posts: 2	Cheers Adam, I'm trying to avoid opening all the workbooks though - once i've done that, i may as well cut n paste the value from D5 into the master file