Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Linking Problem
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 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.. -- soundxplosio ----------------------------------------------------------------------- soundxplosion's Profile: http://www.excelforum.com/member.php...fo&userid=2367 View this thread: http://www.excelforum.com/showthread.php?threadid=37385 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Linking Problem
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 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Linking Problem
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 | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Linking Problem
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 ------------------------------------------------------------------------ soundxplosion's Profile: http://www.excelforum.com/member.php...o&userid=23679 View this thread: http://www.excelforum.com/showthread...hreadid=373859 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Linking Problem
Stil doesn't work :'( maybe i have to mail it to you? the can you take a look -- soundxplosion ------------------------------------------------------------------------ soundxplosion's Profile: http://www.excelforum.com/member.php...o&userid=23679 View this thread: http://www.excelforum.com/showthread...hreadid=373859 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking to file, some formulas work only if file is open | Links and Linking in Excel | |||
Linking to another file using file reference typed cell | Excel Worksheet Functions | |||
Hyperlink: Linking problem when link to file | Excel Discussion (Misc queries) | |||
Problem with linking file - please help!!!!! | Excel Discussion (Misc queries) | |||
Turning a text file name into a search and linking the file as a hyperlink | Excel Discussion (Misc queries) |