File names in formulae

[Naz]

Hi,

I have the following formula in cell A1 in a worksheet.

=[Book1.xls]Sheet1!$A$1

I want to copy this formula across horizontally, but want
it to automatically increment the book number. I.e. B1
would read =[Book2.xls]Sheet1!$A$1 and so on.

Is there a way to do this?

Many thanks

Naz

 

[Arvi Laanemets]

Hi

One way is to use INDIRECT function, but then you must have ALL workbooks
ALWAYS to be opened - otherwise the formula returns an error.
=INDIRECT("'C:\My Documents\[Book" & ROW() & ".xls]Sheet1'!A1")
for A1 (note, you don't need absolute references to cell here, as in string
it doesn't change anyway)

Another way (it applies when you need to create formulas once, and then
leave them as they are):
1. Into A1 enter the formula
="_='C:\My Documents\[Book" & ROW() & ".xls]Sheet1'!A1"
Copy the formula down for as much rows as you have files.
Copy the whole range with formulas, and PasteSpecial values (be sure before,
that cell formats are general)
Activate Replace window, enter _ into search field, and press ReplaceAll.

 

[Govind]

Hi,

Input the numbers like 1,2,3.... in a cells
B1,B2,B3...then use this formula

=INDIRECT("[Book"&B1&"]Sheet1!$A$1")

Regards

Govind.