Ask Your Question
0

how to match a cell when the search region is in a different document

asked 2019-10-09 06:19:12 +0200

arya gravatar image

updated 2019-10-10 17:44:20 +0200

How do I match a cell in one document to a range of cells in another document?

For example, the following works when the search item and search region are in the same document:

=IFERROR(MATCH($A2,D$2:D$500,0),"")

...It correctly displays the line number of the first occurrence of the matching cell.

However, the following does NOT work when the search item and search region are in different documents:

=IFERROR(MATCH($A2,'file:///var/www/html/Projects/reports/Complete_All.xlsx'#$Complete_All.$A$2:$A$500,0),"")

I am using LibreOffice Calc version 6.7.0.3.

Reference: https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_MATCH_function


UPDATE: 2019-10-10 Based on comments, I saved both files as XLSX and checked the cell references. The issue still exists. Here are both the files:

Source File C:\fakepath\test_a.xlsx

*Column A has the source/needle cells

*Column B has the MATCH formula

Range File C:\fakepath\test_b.xlsx

*Column A has the range/haystack cells

edit retag flag offensive close merge delete

Comments

Hi, would you be able to upload a sample of the file your linking out to?

gregors15 gravatar imagegregors15 ( 2019-10-09 11:09:51 +0200 )edit

Yes, I uploaded both files.

arya gravatar imagearya ( 2019-10-10 17:25:10 +0200 )edit

Hi, can you create a new column in the file with the match (test_a) and do Numbervalue(A1) and use this column to do the match.

gregors15 gravatar imagegregors15 ( 2019-10-10 18:14:45 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-10-09 22:09:08 +0200

m.a.riosv gravatar image

updated 2019-10-10 22:06:38 +0200

I have just tested and it works for me with 6.3, verify the file reference is right. Open the csv file and with both open side by side modify the refence to the csv file.

edited 20191010

  1. the data in column A of the file with MATCH is as text not as numbers.

  2. There is an old bug report about the broken links with xlsx files. FILESAVE: Links to external data not preserved for XLSX

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-09 06:19:12 +0200

Seen: 37 times

Last updated: Oct 10