I need an excel formula to look in specific ranges for dates and names and return the value for that corresponding week. See the attached fil. On Sheet 1 I need the number for Agent 1 on Sheet 2 for the respective date.
I know the easy answer is just linking them, but these are actually two different files in different locations. I added them to the same workbook for illustration purposes. And the problem I had with linking, was that if I added or removed rows on the sheet 2, it threw off the cell on sheet 1 ( in the production version of these sheets. I tried adding $ to column and cell, but that didnt help).
I tried to use an index match, but couldnt figure it out.