linking file equation...

[miwarren]

Bare with me as I try to explain this.

I am working with an equation like the one below.

=[648455.xls]Sumbit!$L$9

I need a formula that will look in a cell to get the beginning number for the equation.

Example

Say the number 648455 is in cell C75, I in turn need it to take the number from cell C75 and place it in the equation as seen above and then process that equation. I am not sure if excel can do this, or if I will have to use a macro. You guys always seem to have the solution so I figured I would bounce off you.

Please let me know if you need more info on what I need if this is not clear.

Thanks so much for you help.

Mike (Nashville)

[Anne Troy]

You need INDIRECT, I think.
http://www.officearticles.com/excel/...soft_excel.htm
************
Anne Troy
www.OfficeArticles.com

"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> Bare with me as I try to explain this.
>
> I am working with an equation like the one below.
>
> =[648455.xls]Sumbit!$L$9
>
> I need a formula that will look in a cell to get the beginning number
> for the equation.
>
> Example
>
> Say the number 648455 is in cell C75, I in turn need it to take the
> number from cell C75 and place it in the equation as seen above and
> then process that equation. I am not sure if excel can do this, or if
> I will have to use a macro. You guys always seem to have the solution
> so I figured I would bounce off you.
>
> Please let me know if you need more info on what I need if this is not
> clear.
>
> Thanks so much for you help.
>
> Mike (Nashville)
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
> http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=475955
>

[Ralph]

So the worksheets are not in the same workbook and are saved as individual
files, right? I don't think the indirect function works here. I too am
searching for a way to do this, if you find one let me know and I will do
same.

"miwarren" wrote:

>
> Bare with me as I try to explain this.
>
> I am working with an equation like the one below.
>
> =[648455.xls]Sumbit!$L$9
>
> I need a formula that will look in a cell to get the beginning number
> for the equation.
>
> Example
>
> Say the number 648455 is in cell C75, I in turn need it to take the
> number from cell C75 and place it in the equation as seen above and
> then process that equation. I am not sure if excel can do this, or if
> I will have to use a macro. You guys always seem to have the solution
> so I figured I would bounce off you.
>
> Please let me know if you need more info on what I need if this is not
> clear.
>
> Thanks so much for you help.
>
> Mike (Nashville)
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=475955
>
>

[Harlan Grove]

Ralph wrote...
>So the worksheets are not in the same workbook and are saved as individual
>files, right? . . .

Look at the OP's sample formula,

=[648455.xls]Sumbit!$L$9

Workbook and worksheet names differ, so the syntax is the same as if
the workbook contained multiple worksheets.

> . . . I don't think the indirect function works here. I too am
>searching for a way to do this, if you find one let me know and I will do
>same.

If the workbook is open, INDIRECT does work for this. It's only when
the other workbook is closed that INDIRECT doesn't work. In that case,
see

http://www.google.com/groups?selm=hk...newsranger.com

[Ralph]

I see a cell formula:
="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1"
but I want to reference one folder as "A23" and the file as "B23".xls. then
when I pull the formula down my column I hope it will change the folder and
file to the values in the A and B columns. still not clear to me



"Harlan Grove" wrote:

> Ralph wrote...
> >So the worksheets are not in the same workbook and are saved as individual
> >files, right? . . .
>
> Look at the OP's sample formula,
>
> =[648455.xls]Sumbit!$L$9
>
> Workbook and worksheet names differ, so the syntax is the same as if
> the workbook contained multiple worksheets.
>
> > . . . I don't think the indirect function works here. I too am
> >searching for a way to do this, if you find one let me know and I will do
> >same.
>
> If the workbook is open, INDIRECT does work for this. It's only when
> the other workbook is closed that INDIRECT doesn't work. In that case,
> see
>
> http://www.google.com/groups?selm=hk...newsranger.com
>
>

[Harlan Grove]

Ralph wrote...
>I see a cell formula:
>="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1"
>but I want to reference one folder as "A23" and the file as "B23".xls. then
>when I pull the formula down my column I hope it will change the folder and
>file to the values in the A and B columns. still not clear to me

Adapt the formula.

="='"&A23&"\["&B23&".xls]"&<sheet and range address here>)

[miwarren]

Didn't work for me what about you Ralph? I am still trying and I appreciate those with advice. This one just has me stumped...

Ralph wrote...
>I see a cell formula:
>="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1"
>but I want to reference one folder as "A23" and the file as "B23".xls. then
>when I pull the formula down my column I hope it will change the folder and
>file to the values in the A and B columns. still not clear to me

Adapt the formula.

="='"&A23&"\["&B23&".xls]"&<sheet and range address here>)