using cell info in an equation... Very frusterated

  • Thread starter Thread starter miwarren
  • Start date Start date
M

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)
 
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 said:
=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?action=getinfo&userid=24682
View this thread: http://www.excelforum.com/showthread.php?threadid=476811
Click to expand...
Click to expand...
 
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 said:
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 said:
=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?action=getinfo&userid=24682
View this thread: http://www.excelforum.com/showthread.php?threadid=476811
Click to expand...
Click to expand...
Click to expand...
 
Back
Top