Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
dany_frumusik
 | Femeie
24 ani
Constanta
cauta Barbat
24 - 56 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Source:
I had to link 2 files (1 & 2) over the network and extract data from one to the other and vice-versa.
I also had to use a condition for the extracted data and because of this, in file 2 I needed only values from certain rows from file 1, but to be displayed in consecutive rows in file 2.
Here's the formula I used in file 2 (insert with CTRL+SHIFT+ENTER):
=IFERROR(INDEX('LINK OF FILE 1\[FILENAME 1.xlsm]Sheet1'!H$3:H$384;SMALL(IF('LINK OF FILE 1\[FILENAME 1.xlsm]Sheet1'!K$3:K$384="INC";ROW('LINK OF FILE 1\[FILENAME 1.xlsm]Sheet1'!H$3:H$384)-ROW('LINK OF FILE 1\[FILENAME 1.xlsm]Sheet1'!H$3)+1);ROW('LINK OF FILE 1\[FILENAME 1.xlsm]Sheet1'!H1)));"")
Make sure to replace ; with , depending on your regional settings.
In file 1 I had to display some values from file 2, but only on those rows in file 1 which exist in file 2 (remember I extracted data only from some rows in file 1 to file 2).
Here's the formula I used in file 1 (insert with CTRL+SHIFT+ENTER):
=IFERROR(INDEX('LINK OF FILE 2\[FILENAME 2.xlsm]Sheet1'!V$2:V$13;MATCH(N3&O3;'['LINK OF FILE 2\[FILENAME 2.xlsm]Sheet1'!Q$2:Q$13&'LINK OF FILE 2\[FILENAME 2.xlsm]Sheet1'!R$2:R$13;0));"")
Watch carefully for the placement of $ in these formulas.
Modificat de TRaP (acum 6 ani)
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Important note:
When linking multiple files with a complex formula (like an ARRAY formula), if file formats are .xls/.xlsx (normal excel workbook), when opening one with the other one closed it will ask you for the source file and give you an error about updating links.
You can bypass that if you make both files macro-enabled - .xlsm/.xlsb.
|
|
| pus acum 6 ani |
|