macro index+Match+loop

[hail11]

dim name as string
for x = 1 to 5
name = Sheets("Main").Range("A"& x)
then i need INDEX([wss.xlsm]name!$AC$63:$AC$74,MATCH(C2,[wss.xlsm]name!$AB$63:$AB$74,FALSE),1)

for each sheet it is an associates name to get there pay and hours

I then need to copy this and paste it into

="Main!A"&x

Please help

 

[hail11]

Since you replied that you only need to place the found number in the Cell and not the formula, you could use INDIRECT in a temporary formula, then replace it with the resulting value.

Try...

Sub Lookup_PayData()
    Dim name As String
    Dim x As Long
    
    With Sheets("Main").Range("B1:B5")
        .FormulaR1C1 = "=INDEX(INDIRECT(""[wss.xlsm]"" & RC[-1]&""!$AC$63:$AC$74"")," _
        & "MATCH(R2C3,INDIRECT(""[wss.xlsm]"" & RC[-1]&""!$AB$63:$AB$74""),FALSE),1)"
        .Value = .Value
    End With
End Sub

I was trying it to move to a diffrent worksheet can i ask why this gives me a #REF in the cell now

Sub Lookup_hourswork()
    Dim name As String
    Dim x As Long
    
    With Sheets("Dates").Range("B123:B143")
        .FormulaR1C1 = "=INDEX(INDIRECT(""[David's Team 2011.xlsm]"" & RC[-1]&""!$B$10:$B$374"")," _
        & "MATCH(R122C2,INDIRECT(""[David's Team 2011.xlsm]"" & RC[-1]&""!$A$10:$A$374""),FALSE),1)"
        .Value = .Value
    End With
End Sub

 

[Jerry Sullivan]

I was trying it to move to a diffrent worksheet can i ask why this gives me a #REF in the cell now

Is "David's Team 2011.xlsm" open when you run the macro?
It needs to be. (It would probably be a good idea to add a check that the file is open into the macro, or change to a formula that doesn't require the external reference file to be open).

 

[Jerry Sullivan]

Just noticed one other thing that would need to change due to the reference to your new workbook.

Since the workbook reference has a space in it, you'll need to modify the formula to add single quotes around the
'[Work book]SheetName'

So the resulting reference looks like this...
='[David''s Team 2011.xlsm]Sheet1'!$B$10:$B$374

This wasn't needed when the workbook had no spaces...
=[wss.xlsm]Sheet1!$B$10!$B$10:$B$374