ExcelBanter - Variable cell referencing

There's a standard syntax for pointing your formulae at cells in outside
workbooks. As I recall, it works like this:

[drive:\FileName.xls]SheetName!CellRef

Use single quotes around the whole thing if there's a space in the file
name. But the best way to be sure I'm not steering you wrong is this:

1) Open your home workbook (let's say it's called MyBook.xls).

2) Open the "foreign" workbook (drive:\Path\Foreign.xls), so that both
workbooks are open at the same time.

3) Point your formula to the target cell by the usual arrow-key method. For
example, in R5C3 of MyBook, type "=" and instead of finishing the formulae
just hit <Ctl-Tab, which switches to the next workbook. Use <Ctl-PgUp and
<Ctl-PgDn to navigate to the sheet you want, and the arrow keys to point to
a certain cell, say R12C1. Hit <Enter to complete the formula.

At this point the formula probably reads
"=[Foreign.xls]SheetName!R[7]C[-2]", which tells you something about the
right syntax when the foreign workbook is open but not the rest of the time.
So:

4) Close the other workbook and check the formula again. In my test copy,
at least, now the formula reads

='drive:\path\[Foreign.xls]SheetName'!R[7]C[-2]

Note the placement of the quotes, which is not what I told you above. Not
only does that tell you the correct syntax for referring to outside workbooks
in your formulae, you can use this technique to create the references rather
than typing them all out, which can be laborious when the path is long.

--- "Paul" wrote:
I need to create a formula to reference many different external sheets at
once automatically.

Column A Column B
Name1 \\Home\Folder\Name1.xls
Name2 \\Home\Folder\Name2.xls
Name3 \\Home\Folder\Name3.xls