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


www.Geocities.com/UDQServices
Free Data Processing Add-in<
===== * ===== * ===== * =====


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


"Jim Rech" wrote in message
...
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"

wrote in message
news:soundxplosion.1pl8vu_1117025638.9489@excelfor um-nospam.com...
|
| 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 a 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...o&userid=23679
| View this thread:
http://www.excelforum.com/showthread...hreadid=373859
|