include string or text value in cell reference

N

nelly

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
 
C

CLR

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
 
N

nelly

CLR said:
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.
Click to expand...


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
 
D

Dave Peterson

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.
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
Click to expand...
 
P

Pete_UK

Try this:

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

Hope this helps.

Pete
CLR said:
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.
Click to expand...


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
Click to expand...
 
Top