Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
INDEX/MATCH automatic updates while source files are closed
I was very happy to find out about the possibilities of the INDEX/MATCH
combination...and making links to several source documents. However, as the function MATCH alone is able to update automatically when the source file is closed, the function INDEX/MATCH combination does not (well I hope its possible, but I cant do it). I am working on a destination file containing many links to several source files using the formula: =INDEX(array;row_num;column_num) For array I use an array in the source file, for example [source file x]$A$1:$D$11; For row_num I use for example =MATCH(D3;[source file x]$B$1:$B$11,0) For column_num I use for example =MATCH (A1;[source file x]$A$1:$D$1,0) It works perfectly, and also it updates nicely when the source file is open. But for some reason it does not update when the source file is closed, while other functions do. The source file and destination file are in the same folder on my computer. I am using Excel 2003. Can somebody please help me? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
INDEX/MATCH automatic updates while source files are closed
Index/match formulas *will* work on closed WBs.
How about copying and posting your *exact* formula? The pseudo formulas you posted are not exactly correct. For instance, where's the Sheet name?, and do you have a semi-colon after the look-up value? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "mariekek5" wrote in message ... I was very happy to find out about the possibilities of the INDEX/MATCH combination...and making links to several source documents. However, as the function MATCH alone is able to update automatically when the source file is closed, the function INDEX/MATCH combination does not (well I hope its possible, but I cant do it). I am working on a destination file containing many links to several source files using the formula: =INDEX(array;row_num;column_num) For array I use an array in the source file, for example [source file x]$A$1:$D$11; For row_num I use for example =MATCH(D3;[source file x]$B$1:$B$11,0) For column_num I use for example =MATCH (A1;[source file x]$A$1:$D$1,0) It works perfectly, and also it updates nicely when the source file is open. But for some reason it does not update when the source file is closed, while other functions do. The source file and destination file are in the same folder on my computer. I am using Excel 2003. Can somebody please help me? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
INDEX/MATCH automatic updates while source files are closed
Thanks for your reply! This is the formula as I use it.
=INDEX('[test bestand 1.xls]Blad1'!$A$1:$I$16,MATCH(A2;'[test file 1.xls]Blad1'!$B$1:$B$16;0),MATCH(B1;'[test file 1.xls]Blad1'!$A$1:$I$1;0)) What did you mean by "and do you have a semi-colon after the look-up value?" best wishes, Marieke "Ragdyer" wrote: Index/match formulas *will* work on closed WBs. How about copying and posting your *exact* formula? The pseudo formulas you posted are not exactly correct. For instance, where's the Sheet name?, and do you have a semi-colon after the look-up value? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "mariekek5" wrote in message ... I was very happy to find out about the possibilities of the INDEX/MATCH combination...and making links to several source documents. However, as the function MATCH alone is able to update automatically when the source file is closed, the function INDEX/MATCH combination does not (well I hope its possible, but I cant do it). I am working on a destination file containing many links to several source files using the formula: =INDEX(array;row_num;column_num) For array I use an array in the source file, for example [source file x]$A$1:$D$11; For row_num I use for example =MATCH(D3;[source file x]$B$1:$B$11,0) For column_num I use for example =MATCH (A1;[source file x]$A$1:$D$1,0) It works perfectly, and also it updates nicely when the source file is open. But for some reason it does not update when the source file is closed, while other functions do. The source file and destination file are in the same folder on my computer. I am using Excel 2003. Can somebody please help me? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
INDEX/MATCH automatic updates while source files are closed
The pseudo formula in your OP, as well as the formula you just posted has a
mixture of separators ... commas and semi-colons! BTW, Does the formula you posted actually work with those mixed separators? I'm unaware of an XL version that is able to use both at the same time. There are some add-ins that enable this, but I don't believe that is the issue here. Do I understand that the formula is on a *third* WB? When you open the WB that holds this formula, do you get a dialog window asking if you wish to UPDATE LINKS? And what do you answer? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "mariekek5" wrote in message ... Thanks for your reply! This is the formula as I use it. =INDEX('[test bestand 1.xls]Blad1'!$A$1:$I$16,MATCH(A2;'[test file 1.xls]Blad1'!$B$1:$B$16;0),MATCH(B1;'[test file 1.xls]Blad1'!$A$1:$I$1;0)) What did you mean by "and do you have a semi-colon after the look-up value?" best wishes, Marieke "Ragdyer" wrote: Index/match formulas *will* work on closed WBs. How about copying and posting your *exact* formula? The pseudo formulas you posted are not exactly correct. For instance, where's the Sheet name?, and do you have a semi-colon after the look-up value? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "mariekek5" wrote in message ... I was very happy to find out about the possibilities of the INDEX/MATCH combination...and making links to several source documents. However, as the function MATCH alone is able to update automatically when the source file is closed, the function INDEX/MATCH combination does not (well I hope its possible, but I cant do it). I am working on a destination file containing many links to several source files using the formula: =INDEX(array;row_num;column_num) For array I use an array in the source file, for example [source file x]$A$1:$D$11; For row_num I use for example =MATCH(D3;[source file x]$B$1:$B$11,0) For column_num I use for example =MATCH (A1;[source file x]$A$1:$D$1,0) It works perfectly, and also it updates nicely when the source file is open. But for some reason it does not update when the source file is closed, while other functions do. The source file and destination file are in the same folder on my computer. I am using Excel 2003. Can somebody please help me? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
INDEX/MATCH automatic updates while source files are closed
Thanks again. Yes, the formula does work, hereby another one which is
similar, and works fine as well: =INDEX('D:\Marieke Test\TEST BESTANDEN\[TEST PROTECTION.xls]Sheet1'!$A$1:$AD$418;MATCH(E3;'D:\Marieke Test\TEST BESTANDEN\[TEST PROTECTION.xls]Sheet1'!$R$1:$R$418;0);MATCH("merkstam*";'D:\Marie ke Test\TEST BESTANDEN\[TEST PROTECTION.xls]Sheet1'!$A$1:$AD$1;0)) I use one workbook with the source file and one workbook with the destination file. (as such there is no third workbook). When he asks me to Update Links, I click yes. Actually it does seem to work now. It even works when I insert or delete a row or column in the source file. As the formula searches for a row, column combination. The only thing I cant change in the source file is the column which gives the 'row-num'. For example in this particular formula: when I open de source file and change the column from R to S, and then I close the file and open de destination file....then the destination file doesn't know that the column changes its position... Is there a way to solve this problem? Because with column_num....I ask to look for the column with a specific name in the region: $A$1:$AD$1...so even when I chane column from position it doesn't matter. But with row....I ask for a value in a specific column...and then he gives me the row number....is it possible to find the value in another way?! I hope that you can understand what I mean.. Thanks in advance. Best regards, Marieke "Ragdyer" wrote: The pseudo formula in your OP, as well as the formula you just posted has a mixture of separators ... commas and semi-colons! BTW, Does the formula you posted actually work with those mixed separators? I'm unaware of an XL version that is able to use both at the same time. There are some add-ins that enable this, but I don't believe that is the issue here. Do I understand that the formula is on a *third* WB? When you open the WB that holds this formula, do you get a dialog window asking if you wish to UPDATE LINKS? And what do you answer? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "mariekek5" wrote in message ... Thanks for your reply! This is the formula as I use it. =INDEX('[test bestand 1.xls]Blad1'!$A$1:$I$16,MATCH(A2;'[test file 1.xls]Blad1'!$B$1:$B$16;0),MATCH(B1;'[test file 1.xls]Blad1'!$A$1:$I$1;0)) What did you mean by "and do you have a semi-colon after the look-up value?" best wishes, Marieke "Ragdyer" wrote: Index/match formulas *will* work on closed WBs. How about copying and posting your *exact* formula? The pseudo formulas you posted are not exactly correct. For instance, where's the Sheet name?, and do you have a semi-colon after the look-up value? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "mariekek5" wrote in message ... I was very happy to find out about the possibilities of the INDEX/MATCH combination...and making links to several source documents. However, as the function MATCH alone is able to update automatically when the source file is closed, the function INDEX/MATCH combination does not (well I hope its possible, but I cant do it). I am working on a destination file containing many links to several source files using the formula: =INDEX(array;row_num;column_num) For array I use an array in the source file, for example [source file x]$A$1:$D$11; For row_num I use for example =MATCH(D3;[source file x]$B$1:$B$11,0) For column_num I use for example =MATCH (A1;[source file x]$A$1:$D$1,0) It works perfectly, and also it updates nicely when the source file is open. But for some reason it does not update when the source file is closed, while other functions do. The source file and destination file are in the same folder on my computer. I am using Excel 2003. Can somebody please help me? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic updates of links between files €“ Excel 2007 | Links and Linking in Excel | |||
Automatic updates | Excel Discussion (Misc queries) | |||
How to update destination file with source files closed? | Excel Discussion (Misc queries) | |||
AUTOMATIC UPDATES | Excel Worksheet Functions | |||
Using INDEX w/MATCH to get data from unsorted source | New Users to Excel |