File Linking Problem

[soundxplosion]

Hi, i gonna try to explain my problem but its a bit difficult.

For example:
in A1, A2, A3, i have parts of filenames!
the content of A1 = 2003
A2 = 2004
A3 = 2005
and the file names are: yearresults2003.xls
yearresults2004.xls
yearresults2005.xls
its just an example, for explaining easely,
i have hundreds of numberic files like that

in B1 i like to have a counting result of that file
yes i can link that, but i doesn't go automaticly
i have something like this then in B1:
=COUNT('[yearresults2003.XLT]sheet1'!$D$6:$D$47)

Now on the place 2003 I want to have the content of A1
It would be something like: yearresults(=A1).xls
=Count('[yearresults(=A1).xls]sheet1'!$D$6:$D$47)
But that didn't work, how can i link it with the content of cel A1?

Thats problem 1,

Problem 2 is the file is possibly on to locations... an old and ne
one
How can i make it that he atomatic find the right location

for ex.

c:\excelfiles\newlocation\
c:\excelfiles\oldlocation\

it is no problem to copple a form with it with selection box for th
folder, but what code should i use for that?

AND!

the location is for the formula in B1, B2 of problem 1
sow i get something like
=Count('[c:\excelfiles\newlocation\yearresults(=A1).xls]sheet1'!$D$6:$D$47)

but that didn't work at all, how do i solve that problem

Thx for your support!!!! please help me out
it has to go sow automaticly as possible, but all the solutions ar
welcome..

 

[Jim Rech]

The only built-in way to reference other sheets indirectly as you want is
with the INDIRECT function. You might check it out in Help. But those
sheets must be in open workbooks. INDIRECT will not work with closed files.

--
Jim
"soundxplosion" <[email protected]>
wrote in message
|
| Hi, i gonna try to explain my problem but its a bit difficult.
|
| For example:
| in A1, A2, A3, i have parts of filenames!
| the content of A1 = 2003
| A2 = 2004
| A3 = 2005
| and the file names are: yearresults2003.xls
| yearresults2004.xls
| yearresults2005.xls
| its just an example, for explaining easely,
| i have hundreds of numberic files like that
|
| in B1 i like to have a counting result of that file
| yes i can link that, but i doesn't go automaticly
| i have something like this then in B1:
| =COUNT('[yearresults2003.XLT]sheet1'!$D$6:$D$47)
|
| Now on the place 2003 I want to have the content of A1
| It would be something like: yearresults(=A1).xls
| =Count('[yearresults(=A1).xls]sheet1'!$D$6:$D$47)
| But that didn't work, how can i link it with the content of cel A1?
|
| Thats problem 1,
|
| Problem 2 is the file is possibly on to locations... an old and new
| one
| How can i make it that he atomatic find the right location
|
| for ex.
|
| c:\excelfiles\newlocation\
| c:\excelfiles\oldlocation\
|
| it is no problem to copple a form with it with selection box for the
| folder, but what code should i use for that?
|
| AND!
|
| the location is for the formula in B1, B2 of problem 1
| sow i get something like
|
=Count('[c:\excelfiles\newlocation\yearresults(=A1).xls]sheet1'!$D$6:$D$47)
|
| but that didn't work at all, how do i solve that problem
|
| Thx for your support!!!! please help me out
| it has to go sow automaticly as possible, but all the solutions are
| welcome...
|
|
| --
| soundxplosion
| ------------------------------------------------------------------------
| soundxplosion's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=23679
| View this thread: http://www.excelforum.com/showthread.php?threadid=373859
|

 

[Daniel CHEN]

Suppose

you have year number in A1 like "2003"
you have sheet name in B1 like "sheet1"
starting cell is "D6" and ending cell is "D47"

Then you should use a formula like:

=COUNT(OFFSET(INDIRECT(ADDRESS(6,4,,,"[yearresults"&A1&".xls]"&B1)),0,0,42,1))

(where 6,4 represent D6, 42,1 represents 42 rows (from D6 to D47) and 1
column, "[yearresults"&A1&".xls]" represent workbook file name.

===== * ===== * ===== * =====
Daniel CHEN

(e-mail address removed)
www.Geocities.com/UDQServices

Free Data Processing Add-in<

===== * ===== * ===== * =====


Workbook File Name Sheet Name Starting Row Starting Column Range
Height Range Width Sum Range

The only built-in way to reference other sheets indirectly as you want is
with the INDIRECT function. You might check it out in Help. But those
sheets must be in open workbooks. INDIRECT will not work with closed
files.

--
Jim
"soundxplosion"
<[email protected]>
wrote in message
|
| Hi, i gonna try to explain my problem but its a bit difficult.
|
| For example:
| in A1, A2, A3, i have parts of filenames!
| the content of A1 = 2003
| A2 = 2004
| A3 = 2005
| and the file names are: yearresults2003.xls
| yearresults2004.xls
| yearresults2005.xls
| its just an example, for explaining easely,
| i have hundreds of numberic files like that
|
| in B1 i like to have a counting result of that file
| yes i can link that, but i doesn't go automaticly
| i have something like this then in B1:
| =COUNT('[yearresults2003.XLT]sheet1'!$D$6:$D$47)
|
| Now on the place 2003 I want to have the content of A1
| It would be something like: yearresults(=A1).xls
| =Count('[yearresults(=A1).xls]sheet1'!$D$6:$D$47)
| But that didn't work, how can i link it with the content of cel A1?
|
| Thats problem 1,
|
| Problem 2 is the file is possibly on to locations... an old and new
| one
| How can i make it that he atomatic find the right location
|
| for ex.
|
| c:\excelfiles\newlocation\
| c:\excelfiles\oldlocation\
|
| it is no problem to copple a form with it with selection box for the
| folder, but what code should i use for that?
|
| AND!
|
| the location is for the formula in B1, B2 of problem 1
| sow i get something like
|
=Count('[c:\excelfiles\newlocation\yearresults(=A1).xls]sheet1'!$D$6:$D$47)
|
| but that didn't work at all, how do i solve that problem
|
| Thx for your support!!!! please help me out
| it has to go sow automaticly as possible, but all the solutions are
| welcome...
|
|
| --
| soundxplosion
| ------------------------------------------------------------------------
| soundxplosion's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=23679
| View this thread:
http://www.excelforum.com/showthread.php?threadid=373859
|

 

[soundxplosion]

gonna try it now, sorry for my bad english by the way
i tought you can link with closed files in visual basic...
maybe i can open fil in visual basic
with screen updating on false, sow they would'nt see, then update and
close...

gonna try it, thank you allready

 

[soundxplosion]

Stil doesn't work :'(

maybe i have to mail it to you? the can you take a look