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

--
Kind regards,

Niek Otten

"Bryan" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[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

"Niek Otten" <[email protected]> a écrit dans le message de
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

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