If you want it all in a formula, type:
=INDIRECT(CHOOSE(A1,"[Book2]Sheet1!B1","[Book2]Sheet1!D4","[Book3]Sheet1!B1"
))

Nick's solution is easier to maintain and allows an infinite (65536) number
of cells

HTH,

--
AP

"Niek Otten" a écrit dans le message de
...
Hi Bryan,

Try to avoid spaces in booknames and sheetnames, so you don't need the
apostrophes; put the addresses in C1:C12, like [Book2]Sheet1!B1

=INDIRECT(INDEX(C1:C12,A1))

--
Kind regards,

Niek Otten

"Bryan" wrote in message
...
Hi,

I wonder if there is a formula that will allow me to tell an excel
spreadsheet to look in up to 12 different cells depending on the number
given
in a different cell e.g.

I would like to put the forumla in cell B1

if cell A1 has the number 1 in it, I would like B1 to look at a certain
cell
in another workbook for data e.g. '[book 2.xls]sheet 1'!B1

If cell A1 has the number 2 in it, I would like B1 to look at [book
2.xls]sheet 1'!D4

Can this formula look for up to 12 different cells?

Hope this makes sense and thank you.
Bryan