How to adjust Formula for 2nd Spreadsheet and to Include File Name

[computerlady143]

I finally have my formula but I need to adjust it to include the File name for my link. I am using Index and Match. It works but for every cell I copy the formula in I have to choose the source file. The formula I am using is this:

=INDEX([x]Sheet1!$F$2:$F$500,MATCH(1,INDEX(([x]Sheet1!$A$2:$A$500=B$10)*([x]Sheet1!$C$2:$C$500=$H$6),),0))

I am putting the formula in my spreadsheet called mcc-sample part c.xlsx and I am extracting the data from a spreadsheet called sample e.xlsx, which I am attaching here and you can see how I have the formula enter and working.

I also need to adjust the formula for the 2nd week to put the values on the 2nd worksheet, 3rd Worksheet, etc.

[mrice]

Maybe its easier to use a user defined function.

Create a new module in the VBA editor (Alt F11, Insert - module). Paste in

Please Login or Register  to view this content.

In B12 on any of the sheets paste

=GetValue('[sample%20e(1).xlsx]Sheet1'!$A$2:$A$655,B10,2,$H$6,4)