Search/update data

[noori]

Weeks---Names-----Amount earned
1-------Peter-------100
2-------Jone--------75
3-------William------150
4-------Jone--------25
5-------Peter-------50
1-------William------100
2-------Peter-------150
3-------Jone--------75
4-------Peter-------100
5-------Jone--------50
1-------William------125
2-------Jone--------100
3-------William------150
4-------Jone--------200
5-------Peter-------100

Hello,
I want when I chose the name in the cell name: it shows the compelete history of the whole picture/detial of that person(from the data table shown above.) like which weeks he worked / how much salary he got that week. For example: Cell week and salary. (If week 5 is write two times it add and show one amount).

Second thing is if I write in name(which is list of three nameseter, Jone, William-[data validation-list]) like if I write p then it show peter and if i press W shows william, if i press J then come up jone.

Name-----week-------Salary
Peter-------1--------100
------------2--------150
------------4--------100
------------5--------150
Please any suggestion how can I do. Will be grateful to you.

 

[jim may]

After FIRST Sorting your Table 1) names and 2)Weeks
With the Subtotal Feature I produced the following...

Excel Workbook
	A	B	C
1	Weeks	Names	Amount earned
4	2 Total		175
6	3 Total		75
9	4 Total		225
11	5 Total		50
12		Jone Total	525
14	1 Total		100
16	2 Total		150
18	4 Total		100
21	5 Total		150
22		Peter Total	500
25	1 Total		225
28	3 Total		300
29		William Total	525
30		Grand Total	1550
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C4	=SUBTOTAL(9,C2:C3)
C6	=SUBTOTAL(9,C5:C5)
C9	=SUBTOTAL(9,C7:C8)
C11	=SUBTOTAL(9,C10:C10)
C12	=SUBTOTAL(9,C2:C10)
C14	=SUBTOTAL(9,C13:C13)
C16	=SUBTOTAL(9,C15:C15)
C18	=SUBTOTAL(9,C17:C17)
C21	=SUBTOTAL(9,C19:C20)
C22	=SUBTOTAL(9,C13:C20)
C25	=SUBTOTAL(9,C23:C24)
C28	=SUBTOTAL(9,C26:C27)
C29	=SUBTOTAL(9,C23:C27)
C30	=SUBTOTAL(9,C2:C27)

 

[noori]

Hello sir,

Thanks for your reply, can you please tell me the way i want.

 

[noori]

Can someone help me. Please

 

[noori]

But how can I search and update list without any changes in list.

 

[jim may]

Excel Workbook
	A	B	C	D	E	F	G
1	Weeks	Names	Amount earned		Name	week	Salary
2	1	Peter	100	Select from DropDown -->>>	Peter	1	100
3	2	Jone	75			2	150
4	3	William	150			3	0
5	4	Jone	25			4	100
6	5	Peter	50			5	150
7	1	William	100				500
8	2	Peter	150
9	3	Jone	75
10	4	Peter	100
11	5	Jone	50
12	1	William	125
13	2	Jone	100
14	3	William	150
15	4	Jone	200
16	5	Peter	100
Sheet1

Excel 2007

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(--($B$2:$B$16=E$2),--($A$2:$A$16=F2),C$2:C$16)
G3	=SUMPRODUCT(--($B$2:$B$16=E$2),--($A$2:$A$16=F3),C$2:C$16)
G4	=SUMPRODUCT(--($B$2:$B$16=E$2),--($A$2:$A$16=F4),C$2:C$16)
G5	=SUMPRODUCT(--($B$2:$B$16=E$2),--($A$2:$A$16=F5),C$2:C$16)
G6	=SUMPRODUCT(--($B$2:$B$16=E$2),--($A$2:$A$16=F6),C$2:C$16)
G7	=SUM(G2:G6)

 

[noori]

Work like a Magic

Thank you so much sir for your great help. Can we do this with macro instead formula.

And is it possible when write a word in data validation list it search and show close match.