IF Forumla help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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))
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top