Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default file path: reference to other workbooks

The file links to different workbooks by conditions, I can get the file
name, path, etc,

such as:

in A1: ="c:\abc\"&lastmonth&".xls" -- c:\abc\june.xls

or A1: ="c:\abc\"&"["lastmonth&".xls]" -- c:\abc\[june.xls]

I name A1 as LastFile, how can I use it to reference a cell in that
file? like

=c:\abc\[june.xls]total!a1

I tried a few combinations but didn't work, what is the correct format?
Thank you.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 95
Default file path: reference to other workbooks

You may need to use the function INDIRECT.EXT. It is not on the standard
Excel, you have to download it. Check this page for the syntaxis and check
if it fits your needs:
http://xcell05.free.fr/english/moref...direct.ext.htm

Hope this help,
Miguel.

"muster" wrote:

The file links to different workbooks by conditions, I can get the file
name, path, etc,

such as:

in A1: ="c:\abc\"&lastmonth&".xls" -- c:\abc\june.xls

or A1: ="c:\abc\"&"["lastmonth&".xls]" -- c:\abc\[june.xls]

I name A1 as LastFile, how can I use it to reference a cell in that
file? like

=c:\abc\[june.xls]total!a1

I tried a few combinations but didn't work, what is the correct format?
Thank you.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default file path: reference to other workbooks

In order for this to work, the workbook must be opened. Try

='c:\abc\[june.xls]total'!a1

"muster" wrote:

The file links to different workbooks by conditions, I can get the file
name, path, etc,

such as:

in A1: ="c:\abc\"&lastmonth&".xls" -- c:\abc\june.xls

or A1: ="c:\abc\"&"["lastmonth&".xls]" -- c:\abc\[june.xls]

I name A1 as LastFile, how can I use it to reference a cell in that
file? like

=c:\abc\[june.xls]total!a1

I tried a few combinations but didn't work, what is the correct format?
Thank you.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default file path: reference to other workbooks

Barb Reinhardt wrote...
In order for this to work, the workbook must be opened. Try

='c:\abc\[june.xls]total'!a1

....

If the workbook were open, the formula

=[june.xls]total!A1

would be sufficient because Excel won't load multiple files with the
same base filename, e.g., june.xls, into memory at the same time.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default file path: reference to other workbooks

Thank you all.

My intention at first is getting data from a file that is not opened. I
realised that is probably not feasible. I have to write a function to
do that.

But suppose the file is opened. Now I get my file name in a cell, say
A1, which named "LastFile". What is the correct syntax to reference a
cell in "LastFile"? I can't use explicit file name, because it will
change.

Harlan Grove wrote:
Barb Reinhardt wrote...
In order for this to work, the workbook must be opened. Try

='c:\abc\[june.xls]total'!a1

...

If the workbook were open, the formula

=[june.xls]total!A1

would be sufficient because Excel won't load multiple files with the
same base filename, e.g., june.xls, into memory at the same time.




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default file path: reference to other workbooks

Alternatively, you could use INDIRECT.EXT within the MOREFUNC addin from he

http://xcell05.free.fr/english/index...func_Functions

"muster" wrote:

Thank you all.

My intention at first is getting data from a file that is not opened. I
realised that is probably not feasible. I have to write a function to
do that.

But suppose the file is opened. Now I get my file name in a cell, say
A1, which named "LastFile". What is the correct syntax to reference a
cell in "LastFile"? I can't use explicit file name, because it will
change.

Harlan Grove wrote:
Barb Reinhardt wrote...
In order for this to work, the workbook must be opened. Try

='c:\abc\[june.xls]total'!a1

...

If the workbook were open, the formula

=[june.xls]total!A1

would be sufficient because Excel won't load multiple files with the
same base filename, e.g., june.xls, into memory at the same time.



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default file path: reference to other workbooks

I might have made it complicated or I didn't get it. Let me try to
rephrase my question. INDIRECT.EXT is good but I don't want to use
addins because they need maintainence.

I have got the file name (c:\abc\june.xls") for this month and save it
in a cell, A1, which is named "LastFile".

How can I use "LastFile" or A1 to construct the format
'c:\abc\june.xls\[sheet 1]'!A1, so that when it is opened next time, it
will update from june.xls? (don't need to get value right now).

I thought it's a syntax question, am I wrong?

Thank you.

Barb Reinhardt wrote:
Alternatively, you could use INDIRECT.EXT within the MOREFUNC addin from he

http://xcell05.free.fr/english/index...func_Functions

"muster" wrote:

Thank you all.

My intention at first is getting data from a file that is not opened. I
realised that is probably not feasible. I have to write a function to
do that.

But suppose the file is opened. Now I get my file name in a cell, say
A1, which named "LastFile". What is the correct syntax to reference a
cell in "LastFile"? I can't use explicit file name, because it will
change.

Harlan Grove wrote:
Barb Reinhardt wrote...
In order for this to work, the workbook must be opened. Try

='c:\abc\[june.xls]total'!a1
...

If the workbook were open, the formula

=[june.xls]total!A1

would be sufficient because Excel won't load multiple files with the
same base filename, e.g., june.xls, into memory at the same time.




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default file path: reference to other workbooks

Are you saying that you want to have a formula that constructs the filepath
from other information you have. What you'll need to do is something like
this:

="'c:\abc\" & A1 & "\[sheet 1]'!A1"

You'll probably need to use the indirect function to get it to find what you
want. Let's say this is in B1, in C1 you'll need INDIRECT(B1).

This all assumes that the workbook is open.

HTH,
Barb Reinhardt



"muster" wrote:

I might have made it complicated or I didn't get it. Let me try to
rephrase my question. INDIRECT.EXT is good but I don't want to use
addins because they need maintainence.

I have got the file name (c:\abc\june.xls") for this month and save it
in a cell, A1, which is named "LastFile".

How can I use "LastFile" or A1 to construct the format
'c:\abc\june.xls\[sheet 1]'!A1, so that when it is opened next time, it
will update from june.xls? (don't need to get value right now).

I thought it's a syntax question, am I wrong?

Thank you.

Barb Reinhardt wrote:
Alternatively, you could use INDIRECT.EXT within the MOREFUNC addin from he

http://xcell05.free.fr/english/index...func_Functions

"muster" wrote:

Thank you all.

My intention at first is getting data from a file that is not opened. I
realised that is probably not feasible. I have to write a function to
do that.

But suppose the file is opened. Now I get my file name in a cell, say
A1, which named "LastFile". What is the correct syntax to reference a
cell in "LastFile"? I can't use explicit file name, because it will
change.

Harlan Grove wrote:
Barb Reinhardt wrote...
In order for this to work, the workbook must be opened. Try

='c:\abc\[june.xls]total'!a1
...

If the workbook were open, the formula

=[june.xls]total!A1

would be sufficient because Excel won't load multiple files with the
same base filename, e.g., june.xls, into memory at the same time.




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default file path: reference to other workbooks

Yes. Finally it worked. It must be exatly the same including single
quotes. Yes I have to use indirect() and the workbook must be open.

So I'll check indirect.ext() or write a macro to open & copy the data.

Thanks a lot.


Barb Reinhardt wrote:
Are you saying that you want to have a formula that constructs the filepath
from other information you have. What you'll need to do is something like
this:

="'c:\abc\" & A1 & "\[sheet 1]'!A1"

You'll probably need to use the indirect function to get it to find what you
want. Let's say this is in B1, in C1 you'll need INDIRECT(B1).

This all assumes that the workbook is open.

HTH,
Barb Reinhardt



"muster" wrote:

I might have made it complicated or I didn't get it. Let me try to
rephrase my question. INDIRECT.EXT is good but I don't want to use
addins because they need maintainence.

I have got the file name (c:\abc\june.xls") for this month and save it
in a cell, A1, which is named "LastFile".

How can I use "LastFile" or A1 to construct the format
'c:\abc\june.xls\[sheet 1]'!A1, so that when it is opened next time, it
will update from june.xls? (don't need to get value right now).

I thought it's a syntax question, am I wrong?

Thank you.

Barb Reinhardt wrote:
Alternatively, you could use INDIRECT.EXT within the MOREFUNC addin from he

http://xcell05.free.fr/english/index...func_Functions

"muster" wrote:

Thank you all.

My intention at first is getting data from a file that is not opened. I
realised that is probably not feasible. I have to write a function to
do that.

But suppose the file is opened. Now I get my file name in a cell, say
A1, which named "LastFile". What is the correct syntax to reference a
cell in "LastFile"? I can't use explicit file name, because it will
change.

Harlan Grove wrote:
Barb Reinhardt wrote...
In order for this to work, the workbook must be opened. Try

='c:\abc\[june.xls]total'!a1
...

If the workbook were open, the formula

=[june.xls]total!A1

would be sufficient because Excel won't load multiple files with the
same base filename, e.g., june.xls, into memory at the same time.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro help: File path in Excel 2000 macronewfie Excel Discussion (Misc queries) 2 May 26th 06 02:21 PM
Change of File name within a cell reference Zuzana Excel Discussion (Misc queries) 3 November 30th 05 03:38 PM
How do I reference external data from a file, file name found in . Clux Excel Discussion (Misc queries) 1 February 10th 05 11:52 PM
copy/paste from one file to another without file name reference slvrblt Excel Discussion (Misc queries) 4 February 6th 05 09:57 PM
Weekly Transaction Processing Ralph Howarth Excel Worksheet Functions 4 January 19th 05 06:37 AM


All times are GMT +1. The time now is 09:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"