Hi,

I have a problem where Im building up a file name based on values in a
cell and then doing a sum of a particular column from that file name I
built up. I can do the summing no problem, but cant make the file name
correctly.

I have used the INDIRECT method to build the file name but I need to
have those files actually open as well, which i dont want.

The formual i am using is :

=SUM(INDIRECT("'c:\[doc" & A1 & ".xls]PLA'!$D$1"))

where cell A1 contains the value 200512 and on my c drive I have a file
called
doc200512.xls with a tab called PDA and numeric values in column D.

Can someone please show me how to make the files name up without having
to have the files acutally open?????

I have tried with the INDIRECT function but it only works when the
files are open.


I attached a screen shot where I tried the INDIRECT method.


Thanks


+-------------------------------------------------------------------+
|Filename: untitled.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4141 |
+-------------------------------------------------------------------+

--
southerndandy
------------------------------------------------------------------------
southerndandy's Profile: http://www.excelforum.com/member.php...o&userid=29806
View this thread: http://www.excelforum.com/showthread...hreadid=495147

There is no built in way that will fix this, here's a link to some options
(i have never really needed it but I can confirm that using Harlan Grove's
UDF Pull works and I also believe the latest version of Laurent Longre's
add-in will work


ftp://members.aol.com/hrlngrv/

look for pull.zip



or

http://xcell05.free.fr/

look for morefunc and in particular indirect.ext

so you need to install these UDFs

http://www.mvps.org/dmcritchie/excel/install.htm

that should get you going

--
Regards,

Peo Sjoblom

(No private emails please)


"southerndandy"
wrote in message
news:southerndandy.20e1gm_1135165202.2363@excelfor um-nospam.com...

Hi,

I have a problem where Im building up a file name based on values in a
cell and then doing a sum of a particular column from that file name I
built up. I can do the summing no problem, but cant make the file name
correctly.

I have used the INDIRECT method to build the file name but I need to
have those files actually open as well, which i dont want.

The formual i am using is :

=SUM(INDIRECT("'c:\[doc" & A1 & ".xls]PLA'!$D$1"))

where cell A1 contains the value 200512 and on my c drive I have a file
called
doc200512.xls with a tab called PDA and numeric values in column D.

Can someone please show me how to make the files name up without having
to have the files acutally open?????

I have tried with the INDIRECT function but it only works when the
files are open.


I attached a screen shot where I tried the INDIRECT method.


Thanks


+-------------------------------------------------------------------+
|Filename: untitled.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4141 |
+-------------------------------------------------------------------+

--
southerndandy
------------------------------------------------------------------------
southerndandy's Profile:
http://www.excelforum.com/member.php...o&userid=29806
View this thread: http://www.excelforum.com/showthread...hreadid=495147

Can't you use the concatenate function to accomplish this?

"southerndandy" wrote:


Hi,

I have a problem where Im building up a file name based on values in a
cell and then doing a sum of a particular column from that file name I
built up. I can do the summing no problem, but cant make the file name
correctly.

I have used the INDIRECT method to build the file name but I need to
have those files actually open as well, which i dont want.

The formual i am using is :

=SUM(INDIRECT("'c:\[doc" & A1 & ".xls]PLA'!$D$1"))

where cell A1 contains the value 200512 and on my c drive I have a file
called
doc200512.xls with a tab called PDA and numeric values in column D.

Can someone please show me how to make the files name up without having
to have the files acutally open?????

I have tried with the INDIRECT function but it only works when the
files are open.


I attached a screen shot where I tried the INDIRECT method.


Thanks


+-------------------------------------------------------------------+
|Filename: untitled.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4141 |
+-------------------------------------------------------------------+

--
southerndandy
------------------------------------------------------------------------
southerndandy's Profile: http://www.excelforum.com/member.php...o&userid=29806
View this thread: http://www.excelforum.com/showthread...hreadid=495147

No he can't

--
Regards,

Peo Sjoblom

(No private emails please)


"Gwen H" wrote in message
...
Can't you use the concatenate function to accomplish this?

"southerndandy" wrote:


Hi,

I have a problem where Im building up a file name based on values in a
cell and then doing a sum of a particular column from that file name I
built up. I can do the summing no problem, but cant make the file name
correctly.

I have used the INDIRECT method to build the file name but I need to
have those files actually open as well, which i dont want.

The formual i am using is :

=SUM(INDIRECT("'c:\[doc" & A1 & ".xls]PLA'!$D$1"))

where cell A1 contains the value 200512 and on my c drive I have a file
called
doc200512.xls with a tab called PDA and numeric values in column D.

Can someone please show me how to make the files name up without having
to have the files acutally open?????

I have tried with the INDIRECT function but it only works when the
files are open.


I attached a screen shot where I tried the INDIRECT method.


Thanks


+-------------------------------------------------------------------+
|Filename: untitled.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4141 |
+-------------------------------------------------------------------+

--
southerndandy
------------------------------------------------------------------------
southerndandy's Profile:
http://www.excelforum.com/member.php...o&userid=29806
View this thread:
http://www.excelforum.com/showthread...hreadid=495147