Workbook linking

[matman]

I have approx 100 workbooks. I have another spreasheet that is to link to each of these to look at certain cells for data. The formula used is
='[spreadsheetname.xls] sheet1'! $a$1.
I have copied and pasted all the names into column A, in column B I want to have my formula. Is there any way I can drag the formula down with the spreasheet name looking at Colomn A rather than having to type them all in. This is a task that is to be repeated anumber of times.

 

[Alriemer]

Use the INDIRECT function. It reads text strings as cell addresses. Therefore INDIRECT("a1") returns the contents of A1 but INDIRECT(a1) returns the contents of the cell specified by an address entered as text in A1. That is, if A1 contained "Sheet2!C1" INDIRECT(A1) would return the contents of Sheet2!C1.

Using concatenation and other tricks you can quickly construct cell or file "references" to read with INDIRECT. ie
Col A contains file names
Col B contains sheet names
Col C contains ="["A#&"]"B#&"!C2"
Col D contains =INDIRECT(C#)

This would return the contents of cell C2 on each specified sheet/workbook.

Hope this helps.
Alex
This message was edited by Alriemer on 2002-09-25 02:17

 

[matman]

Thank you very much Alex this is of great use.