using cell info in an equation... Very frusterated

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)

=Indirect("[" & C75 & ".xls]Sumbit!L9")


--
Regards,
Tom Ogilvy



"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=476811
>

the formula you showed

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

Only works for an open file. If you expected the file to be closed you
should have illustrated with

='C:\My Folder\[648455.xls]Sumbit!$L$9'
then time wouldn't have been wasted providing the Indirect answer (for which
it is stated in help on Indirect that is doesn't work with a closed file)
and you wouldn't have had to waste time reinventing the wheel.

Anyway,

the easy solution is to build your hardcoded formula with code using the
value in your cell.

If you insist on a formula solution, you can search Google Groups for author
Harlan Grove and his PULL routine. This is a UDF he wrote which will start
a new instance of excel, open the file, extract the data, close the file,
close the instance of excel and return the result. If you are scanning 300
files, that doesn't sound practical to me, but what you mean by scanning 300
files isn't necessarily definitive either.

--
Regards,
Tom Ogilvy



"miwarren" <[email protected]> wrote in
message news:[email protected]...
>
> I got a #REF! error, any other suggestions.
>
> I just figured it out, I have to open the file, unfortunately this
> needs to scan about 300 files which opening all those everytime is
> probably not the best solution. Any other ideas?
>
> Thanks
>
> Tom Ogilvy Wrote:
> > =Indirect("[" & C75 & ".xls]Sumbit!L9")
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "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=476811
> > >
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile:
http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=476811
>