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
  • Last Modified:

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
Asked:
wrt1mea
  • 3
  • 3
  • 3
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
 
jppintoCommented:
"on Sheet 2 for the respective date..." what is the respective date corresponding to row 1 of sheet1? Beginning Date or Ending Date?
0
 
wrt1meaAuthor Commented:
Ending Date, sorry!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
 
jppintoCommented:
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))

Please check attached file.

jppinto
5-3-11.xlsx
0
 
wrt1meaAuthor Commented:
Teylyn,

I am working on the production report now using your formula. I think its gonna work, but let me confirm...thanks!
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
 
jppintoCommented:
Sorry...copyed the formulas wrongly.

Please check the attached file.

jppinto
5-3-11.xlsx
0
 
wrt1meaAuthor Commented:
Thanks for the fast resposes! Look for more questions from me...
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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