macro index+Match+loop

hail11

Board Regular
Joined
May 3, 2007
Messages
199
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
1. Do you need to place those formulas into "Main!A"&x, or do you just need to place the resulting values from looking up the Pay or Hours?

2. Are you sure the (formula or value) should get pasted into "Main!A"&x ?
It appears that's where your names are currently. Did you mean "Main!B"&x or do you want to overwrite the names now in "Main!A"&x ?
 

alvinwlh

Active Member
Joined
Feb 16, 2009
Messages
305
Hi,

Can try formula at Main --> anycell

Code:
=INDEX(INDIRECT("[wss.xlsm]" & A1&"!$AC$63:$AC$74"),MATCH(C2,INDIRECT("[wss.xlsm]" & A1&"!$AB$63:$AB$74"),FALSE),1)
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi,

Can try formula at Main --> anycell

Code:
=INDEX(INDIRECT("[wss.xlsm]" & A1&"!$AC$63:$AC$74"),MATCH(C2,INDIRECT("[wss.xlsm]" & A1&"!$AB$63:$AB$74"),FALSE),1)

Hi alvinwlh,

INDIRECT will work if the wss.xlsm workbook is open, but not if it is closed.
That was part of the reason I asked if the OP needs the formula to stay in the cell or just the resulting value.

If the formula will be entered by VBA and has to stay in the cell, I think the INDEX-MATCH formula will be better without INDIRECT.
 

alvinwlh

Active Member
Joined
Feb 16, 2009
Messages
305

ADVERTISEMENT

Thanks JS

I am new to INDIRECT :), Just tried out first time.

Glad to know that.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Thanks JS

I am new to INDIRECT :), Just tried out first time.

Glad to know that.

You're welcome. I learned that the hard way...It's a shocking experience to reopen a workbook that was fine 1 minute ago and see #REF! everywhere. :biggrin:
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787

ADVERTISEMENT

Spam
 
Last edited:

hail11

Board Regular
Joined
May 3, 2007
Messages
199
1. Do you need to place those formulas into "Main!A"&x, or do you just need to place the resulting values from looking up the Pay or Hours?

2. Are you sure the (formula or value) should get pasted into "Main!A"&x ?
It appears that's where your names are currently. Did you mean "Main!B"&x or do you want to overwrite the names now in "Main!A"&x ?

1) I need to place the found numbers into "Main!A"&x
2) Sorry I meant "Main!B"&x
 

hail11

Board Regular
Joined
May 3, 2007
Messages
199
Hi alvinwlh,

INDIRECT will work if the wss.xlsm workbook is open, but not if it is closed.
That was part of the reason I asked if the OP needs the formula to stay in the cell or just the resulting value.

If the formula will be entered by VBA and has to stay in the cell, I think the INDEX-MATCH formula will be better without INDIRECT.

The WSS workbook will not always be open I have to open it with macro(this i have coded) then I want to run the code off of the report that transfers into workbook wss.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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...
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,553
Messages
5,596,807
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top