IF Forumla help

[Bryan]

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

 

[Niek Otten]

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))

 

[Ardus Petus]

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

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

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

 

[Bryan]

Thanks guys this has solved the issue and helped considerably.

thanks a lot.
Bryan