include string or text value in cell reference

Hi,

does anyone know how/if i can include a cell value within a path in a
function/formula so i can update it by simply changing cell value,
e.g.


SUM($A$1, '[E:\folder\stats June.xls]'!sheet1$A$1)

so that the 'June' part can change according to a cell value, say F1.
So if I change F1 from "June" to "July" then the SUM formula would
change to show ....'\stats July' .

many thanks in advance, Nelly

Use the INDIRECT() function
--
Gary''s Student


"nelly" wrote:

> Hi,
>
> does anyone know how/if i can include a cell value within a path in a
> function/formula so i can update it by simply changing cell value,
> e.g.
>
>
> SUM($A$1, '[E:\folder\stats June.xls]'!sheet1$A$1)
>
> so that the 'June' part can change according to a cell value, say F1.
> So if I change F1 from "June" to "July" then the SUM formula would
> change to show ....'\stats July' .
>
> many thanks in advance, Nelly
>
>

Check out the INDIRECT Worksheet Function......

Returns the reference specified by a text string. References are immediately
evaluated to display their contents. Use INDIRECT when you want to change the
reference to a cell within a formula without changing the formula itself.

Vaya con Dios,
Chuck, CABGx3



"nelly" wrote:

> Hi,
>
> does anyone know how/if i can include a cell value within a path in a
> function/formula so i can update it by simply changing cell value,
> e.g.
>
>
> SUM($A$1, '[E:\folder\stats June.xls]'!sheet1$A$1)
>
> so that the 'June' part can change according to a cell value, say F1.
> So if I change F1 from "June" to "July" then the SUM formula would
> change to show ....'\stats July' .
>
> many thanks in advance, Nelly
>
>

CLR wrote:
> Check out the INDIRECT Worksheet Function......
>
> Returns the reference specified by a text string. References are immediately
> evaluated to display their contents. Use INDIRECT when you want to change the
> reference to a cell within a formula without changing the formula itself.


Hi, can't quite figure out how I should use indirect - if I currently
have this function

SUM($A$3, '[E:\folder\stats June.xls]sheet1'!$A$1) in a cell

and cell F1 currently holds the text June - I want to then change the
text in F1 to hold July and the above formula to then change to the
following automatically

SUM($A$3, '[E:\folder\stats July.xls]sheet1'!$A$1)

please can you elaborate - thank you

You'd want to use the =indirect() function, but that only works when the
"sending" workbook is open.

But Harlan Grove wrote a UDF called PULL that will retrieve the value from a
closed workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

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

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

nelly wrote:
>
> Hi,
>
> does anyone know how/if i can include a cell value within a path in a
> function/formula so i can update it by simply changing cell value,
> e.g.
>
> SUM($A$1, '[E:\folder\stats June.xls]'!sheet1$A$1)
>
> so that the 'June' part can change according to a cell value, say F1.
> So if I change F1 from "June" to "July" then the SUM formula would
> change to show ....'\stats July' .
>
> many thanks in advance, Nelly

--

Dave Peterson

Try this:

=SUM($A$3,INDIRECT("'[E:\folder\stats "&F1&".xls]sheet1'!$A$1"))

Hope this helps.

Pete

nelly wrote:
> CLR wrote:
> > Check out the INDIRECT Worksheet Function......
> >
> > Returns the reference specified by a text string. References are immediately
> > evaluated to display their contents. Use INDIRECT when you want to change the
> > reference to a cell within a formula without changing the formula itself.
>
>
> Hi, can't quite figure out how I should use indirect - if I currently
> have this function
>
> SUM($A$3, '[E:\folder\stats June.xls]sheet1'!$A$1) in a cell
>
> and cell F1 currently holds the text June - I want to then change the
> text in F1 to hold July and the above formula to then change to the
> following automatically
>
> SUM($A$3, '[E:\folder\stats July.xls]sheet1'!$A$1)
>
> please can you elaborate - thank you