Hi does anyone know how I can break up a formula, so that I can specifiy the
variables by referencing to a cell containing the info that the formula needs

i.e. "=VLOOKUP(B1,[Book5]Sheet1!$A$1:$C$6,2,FALSE)"

How can I break up the above so that instead of specifying the book and
sheet I can just ask it to refer to a particular cell which would contain the
Book and Sheet.

Thanks

"=VLOOKUP(B1,Indirect(A5)2,FALSE)"

Note that Indirect will not work if the workbook reference in cell A5 is to
a closed workbook.

--
Regards,
Tom Ogilvy


"Superfly" wrote in message
...
Hi does anyone know how I can break up a formula, so that I can specifiy
the
variables by referencing to a cell containing the info that the formula
needs

i.e. "=VLOOKUP(B1,[Book5]Sheet1!$A$1:$C$6,2,FALSE)"

How can I break up the above so that instead of specifying the book and
sheet I can just ask it to refer to a particular cell which would contain
the
Book and Sheet.

Thanks

Try using the INDIRECT function:

=VLOOKUP(A1,INDIRECT(C1),2)

This worked for me where C1 held "[book2]Sheet1!G1:H4".

If you want to break the pieces up further your indirect may look something
like:

INDIRECT(C1&D1&E1)

Note that INDIRECT works only with open workbooks.

--
Jim
"Superfly" wrote in message
...
| Hi does anyone know how I can break up a formula, so that I can specifiy
the
| variables by referencing to a cell containing the info that the formula
needs
|
| i.e. "=VLOOKUP(B1,[Book5]Sheet1!$A$1:$C$6,2,FALSE)"
|
| How can I break up the above so that instead of specifying the book and
| sheet I can just ask it to refer to a particular cell which would contain
the
| Book and Sheet.
|
| Thanks
|
|

One thing to note in the solutions already given (which are excelent).
Indirect is a volitile function, which means that it always recalculates.
Vlookup is also a relativelye slow function to evaluate. So if you intend to
have thousands of these formulas in a given workbook then there will be a
noticable performance lag. Probably not a problem but it is something to
consider. For further info see

http://www.decisionmodels.com/calcsecretsi.htm

HTH

"Superfly" wrote:

Hi does anyone know how I can break up a formula, so that I can specifiy the
variables by referencing to a cell containing the info that the formula needs

i.e. "=VLOOKUP(B1,[Book5]Sheet1!$A$1:$C$6,2,FALSE)"

How can I break up the above so that instead of specifying the book and
sheet I can just ask it to refer to a particular cell which would contain the
Book and Sheet.

Thanks