use data in cell as a formula for another cell

[NoelH]

Hi
I have in cell a1 = 2007 and a2 = 2008 and then I wish to input the results
of a lookup to another file which uses the data from ColA eg
COUNTIF([2008_04_apr_Creation.xls]Sheet4!$Y:$Y,"#N/A") I need to change 2008
to 2007.
Thanks for any help, or ideas, Noel

 

[carlo]

Have a look at the "indirect" function in help.

COUNTIF(INDIRECT("[" & A1 & "_04_apr_Creation.xls]Sheet4!$Y:$Y"),"#N/
A")

hth
Carlo

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Hi
I have in cell a1 = 2007 and a2 = 2008 and then I wish to input the results
of a lookup to another file which uses the data from ColA eg
COUNTIF([2008_04_apr_Creation.xls]Sheet4!$Y:$Y,"#N/A") I need to change 2008
to 2007.
Thanks for any help, or ideas, Noel

 

[Dave Peterson]

ps. =countif() won't work with a closed workbook, either.

But there are workarounds for that, too--using =sumproduct() is one alternative.

Hi
I have in cell a1 = 2007 and a2 = 2008 and then I wish to input the results
of a lookup to another file which uses the data from ColA eg
COUNTIF([2008_04_apr_Creation.xls]Sheet4!$Y:$Y,"#N/A") I need to change 2008
to 2007.
Thanks for any help, or ideas, Noel