Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 182

# I need an excel formula

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.
5-3-11.xlsx
0
wrt1mea
• 3
• 3
• 3
2 Solutions

Microsoft MVP ExcelCommented:
Hello,

not sure if this is what you're after:

=INDEX(Sheet2!\$A\$1:\$J\$107,MATCH(Sheet1!B\$1,Sheet2!\$A\$1:\$A\$107,1),MATCH(Sheet1!\$A3,Sheet2!\$A\$1:\$J\$1,0))

cheers, teylyn
0

Commented:
"on Sheet 2 for the respective date..." what is the respective date corresponding to row 1 of sheet1? Beginning Date or Ending Date?
0

Author Commented:
Ending Date, sorry!
0

Microsoft MVP ExcelCommented:
The formula I posted above will show the number for the week ending with the date on Sheet1. If this is not what you need, please explain a bit more, or manually fill in a few values in your sample file and post again.

cheers, teylyn
0

Commented:
Here's my proposal:

=SUMPRODUCT((Sheet2!\$C\$1:\$J\$1=Sheet1!\$A3)*(Sheet2!\$B\$2:\$B\$107=Sheet1!J\$1)*(Sheet2!\$C\$2:\$J\$107))

jppinto
5-3-11.xlsx
0

Author Commented:
Teylyn,

I am working on the production report now using your formula. I think its gonna work, but let me confirm...thanks!
0

Microsoft MVP ExcelCommented:
jppinto, your suggestion shows the following values for Agent 1

1/2      1/9      1/16      1/23      1/30      2/6      2/13

3      1      1      2      1      1      2

But the numbers on Sheet 2 show

Ending Date      Agent 1
2/01/2011      0
9/01/2011      1
16/01/2011      3
23/01/2011      2
30/01/2011      7
6/02/2011      0
13/02/2011      2

Totally different, don't you think?

0

Commented:
Sorry...copyed the formulas wrongly.

jppinto
5-3-11.xlsx
0

Author Commented:
Thanks for the fast resposes! Look for more questions from me...
0

## Featured Post

• 3
• 3
• 3
Tackle projects and never again get stuck behind a technical roadblock.