Sumproduct or ??? for non-same dimensions

[Guest]

Not sure if this is possible, but here's my spreadsheet:


SHEET1
A B C D E
ZZ
1 1/5/06 1/5/06 1/12/06 1/12/06 etc. thru 12/31/06
2 Name Accr Taken Accr Taken
3 John 1.75 0 1.75 -8
4 Jack 1.75 0 1.75 0
5 Jason 1.75 -8 1.75 0

On a separate spreadsheet, I want to create a summary by name. So I would
just input a name and the amount accrued and taken would populate. Here's the
format I was hoping for (second sheet):

SHEET2

A B C

1 Name: __________ (input)
2
3 Vac Accr Vac Used
4 01/05/06 zz xx
5 01/12/06 xx xx
6 01/19/06 xx xx
etc xx xx
... xx xx
12/31/06 xx xx

I know sumproduct doesn't work, but I need something with this type of logic
in the 'zz' cell on sheet2:

sumproduct
(--(sheet1!'A3:A5=sheet2!'B1),--(sheet1!'B1:ZZ1=sheet2!'A4),sheet1!'B3:ZZ5)

Even a pivot table would be cumbersome, as there's way too many columns. Any
ideas? Thanks in advance!

 

[Guest]

You may use MATCH and INDEX, in this case for zz:
=INDEX(Sheet1!$B$3:$E$5,MATCH($B$1,Sheet1!$A$3:$A$5,0),MATCH($A4,Sheet1!$B$1:$E$1,0))
I would recommend to use names for all the Sheet1 ranges, that can the
formula easier to read.

Hope this helps,
Miguel.

 

[Biff]

Hi!

Enter this formula on Sheet2 B4:

=INDEX(Sheet1!$B$3:$E$5,MATCH($B$1,Sheet1!$A$3:$A$5,0),MATCH($A4,Sheet1!$B$1:$E$1,0)+COLUMNS($A:A)-1)

Copy across to C4 then down as needed.

Biff

 

[Guest]

These worked beautifully. Thanks so much!!!!

Hi!

Enter this formula on Sheet2 B4:

=INDEX(Sheet1!$B$3:$E$5,MATCH($B$1,Sheet1!$A$3:$A$5,0),MATCH($A4,Sheet1!$B$1:$E$1,0)+COLUMNS($A:A)-1)

Copy across to C4 then down as needed.

Biff