INDEX from different workbook

[MKnightsS17]

Hi all,

is there a way to perform an action in VBA that works similarly to the INDEX function but interfaces between workbooks.

To give an idea of what I mean, this is the relevant part of a code I have used to index between two worksheets in the same workbook, earlier in the macro:

Dim Ps As Worksheet
 
Set Ps = Worksheets("PersonSummary")
 
LcPs = Ps.Range("A1").SpecialCells(xlCellTypeLastCell).Column
LrPs = Ps.UsedRange.Find("*", Searchdirection:=xlPrevious, searchorder:=xlByRows).Row
 
For n = 2 To LrPs
Ps.Cells(n, LcPs + 1).Formula = "[COLOR=red]=INDEX(JobSummary!B:B,MATCH(C" & n & ",JobSummary!C:C,0))[/COLOR]"
Next n

Where Ps is a worksheet and JobSummary is a worksheet in the same workbook, and highlighted is the formula that works in the relevant cells.


The only method I can think of (please bear with me) involves creating a temporary sheet in the same workbook as the (hypothetical) JobSummay sheet; copying the relevant table from the (hypothetical) Ps sheet into it; putting the formula into this temporary sheet using this method i have above; then copying the relevant new indexed column back across to the other workbook; Then closing the temporary sheet. (phew!)

But with the amount of data im using this is both messy and would add alot of runtime to the macro.

Any ideas?

thanks

 

[Andrew Poulsom]

You can use INDEX/MATCH to refer to a closed workbook. Example:

Ps.Cells(n, LcPs + 1).Formula = "=INDEX('C:\[Source.xls]JobSummary'!B:B,MATCH(C" & n & ",'C:\[Source.xls]JobSummary'!C:C),0)"

 

[MKnightsS17]

Thank you Andrew that is a great help!

 

[MKnightsS17]

What would be an even greater help would be if I could use a reference to a workbook object rather than the workbook name as this could change.

Since the workbook is already open I am currently using:

For n = 2 To Inlr
Intl.Cells(n, Inlc + 1).Formula = "=INDEX('[v9mkworking_JobType3.xls]MASTER JOB LIST'!G:G,MATCH(C" & n & ",'[v9mkworking_JobType3.xls]MASTER JOB LIST'!C:C,0))"
Intl.Cells(n, Inlc + 2).Formula = "=INDEX('[v9mkworking_JobType3.xls]MASTER JOB LIST'!H:H,MATCH(C" & n & ",'[v9mkworking_JobType3.xls]MASTER JOB LIST'!C:C,0))"
Intl.Cells(n, Inlc + 3).Formula = "=INDEX('[v9mkworking_JobType3.xls]MASTER JOB LIST'!J:J,MATCH(C" & n & ",'[v9mkworking_JobType3.xls]MASTER JOB LIST'!C:C,0))"
Next n

with just the workbook and then worksheet, excluding the directory. I have tried inluding a workbook object as that I have already defined, but that doesn't work the way I'm writing it. i.e

Dim [COLOR=red]All[/COLOR] As Workbook
 
Set [COLOR=red]All[/COLOR] = ThisWorkbook
 
For n = 2 To Inlr
Intl.Cells(n, Inlc + 1).Formula = "=INDEX('[[COLOR=red]All[/COLOR]]MASTER JOB LIST'!G:G,MATCH(C" & n & ",'[[COLOR=red]All][/COLOR]MASTER JOB LIST'!C:C,0))"
Next n

Is there a way to include this properly?

Thanks again

 

[Andrew Poulsom]

Try:

Intl.Cells(n, Inlc + 1).Formula = "=INDEX('[" & All.Name & "]MASTER JOB LIST'!G:G,MATCH(C" & n & ",'[" & All.Name & "MASTER JOB LIST'!C:C,0))"

 

[MKnightsS17]

thanks Andrew