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

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

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

Thanks guys this has solved the issue and helped considerably.

thanks a lot.
Bryan

"Bryan" wrote:

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