The formula you just posted looks proper now, in that you're using a single
separator, the semi-colon.
As to your issue of revising the source file:
The destination WB can remain closed for data changes.
Simply have the destination file open when you make *address* changes to the
source file.
XL should update the formulas for you, automatically.
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
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:\Marieke
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 said:
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 said:
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 said:
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
!
--------------------------------------------------------------------------
-
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?
