INDEX/MATCH on different documents (Server)

Hi,

I encountered a problem today that I'm not able to solve.
I made an INDEX MATCH function, the problem now is, it is working on my Laptop, but if i transfer it to the Computer at work i get a heck load of errors. At work I'm trying to pull information from a sheet on a server to my own sheet so I don't have to do this by hand. I tried a lot but I always encountered an error and couldnt get it to work.

=INDEX(INDIRECT("[Planungstool_L4752_1 4 new.xlsm]PERSONALPLANUNG!"&ADRESS(9;3166+4*((COLUMN(A1)-1)/2))&":"&ADRESS(384;3166+4*((COLUMN(A1)-1)/2)));MATCH($A12;INDIRECT("[Planungstool.xlsm]PERSONALPLANUNG!"&ADRESSE(9;3164+4*((COLUMN(A1)-1)/2))&":"&ADRESS(384;3164+4*((COLUMN(A1)-1)/2)));0))

That is the Formula which is not working. It either gives me an eroor at ADRESS or somewhere in the name of the document.
Any ideas?
Thx for any help
Joel

Hi there,

It's not possible for me to test this solution fully, but here's a suggestion which might be helpful:

Define two single-cell named ranges ("ptrWorkbook_1" and "ptrWorkbook_2") in a suitable area of your worksheet.

In the first of these ranges (cells) enter the value "Planungstool_L4752_1 4 new.xlsm"

In the second of these ranges enter the value "Planungstool.xlsm"

Now use the following version of your formula in whichever cells are appropriate (I've used "ADDRESS" instead of "ADRESS" and "," instead of ";" to avoid errors on my English language installation, so you should use "ADRESS" and ";" as before):

=INDEX(INDIRECT("["  &  ptrWorkbook_1  &  "]PERSONALPLANUNG!"  &  ADDRESS(9,  3166 + 4 * ((COLUMN(A1) - 1) / 2))  &  ":"  &  ADDRESS(384,  3166 + 4 * ((COLUMN(A1) - 1) / 2))),  MATCH($A12,  INDIRECT("[" & ptrWorkbook_2 & "]PERSONALPLANUNG!"  &  ADDRESS(9,  3164 + 4 * ((COLUMN(A1) - 1) / 2))  &  ":"  &  ADDRESS(384,  3164 + 4 * ((COLUMN(A1) - 1) / 2))),  0))

Now, when using your workbook at your work installation, replace the values in the cells "ptrWorkbook_1" and "ptrWorkbook_2" with the workbook names appropriate to that installation. You're probably aware that the INDIRECT function will return an error unless the referenced workbook is open.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated

Hope my suggestion helps.

Best regards,

Greg M