how to use Index/match function to call multiple values?

[Legacy 163032]

in trouble and desperate to get this working..

match/index function how does it work to recall all values - say i want all the values (appendix 2) currently on the database to be shown on the gant chart which is appendix 1 below?

Appendix 1

----A---------------------B-----------C-------------D--------------E
A1= Wk commencing____[4/4/11]____[11/4/11]____[18/4/11]____[25/4/11]
A2= Activity1__________[£760]__________________[£1500]_______[£350]_
3= Activity 2______________________[£350]_________________________
4= Activity 3__________[£550]________[£259]_____[£450]_____________
5= Activity 4__________[£1546]__________________[£460]_____________



datasheet, where data is being extracted has the following

appendix 2
list of the activities
----A---------------------B-----------C-------------D--------------E
A1= Description______WK Comm______Value_

A1 = Activity 1 ______4/4/11_________760
A2 = Activity 2 ______11/4/11________350
A3 = Activity 3 ______4/4/11_________550
A4 = Activity 3 ______11/4/11________259
A5 = Activity 3 ______18/4/11________450
A6 = Activity 4 ______4/4/11_________1546
A7 = Activity 4 ______18/4/11________460


at the moment i have this formula =IF(B$1=Database!$B:$B,INDEX(Database!$C:$C,MATCH($A1,Database!$A:$A,0)),"")

so far this work fine, but it does not add anymore values other than once.

my question will be - how to make all the value appear under their week commencing indicated in both database and gant chart.

i must say - this is crucial and is cosing so much of a distress above all. i sense the hard work is over and nearly there- but still need a soft touch round the edges..... perhaps to add another match index (which i don't know where)?

although i do need your support and i thank you for it.

deferdina

 

[number kruncher]

If you have Excel 2007/10 then I would use SUMIFS, eg.

Reference Appendix 2 to create Appendix 1

=sumifs($c$1:$c$7,$a$1:$a$7,$a2,$b$1:$b$7,b$1)

 

[Legacy 163032]

If you have Excel 2007/10 then I would use SUMIFS, eg.

Reference Appendix 2 to create Appendix 1

=sumifs($c$1:$c$7,$a$1:$a$7,$a2,$b$1:$b$7,b$1)

hey! thanks

i tried this formula you have given me but still not working.... none of the values are being transferred from appendix 2 into appendix 1.

although i forgot to mention. appendix two is on other worksheet (say worksheet 2)

 

[number kruncher]

Can you create the summary in Workbook with Appendix 2, then use a simple INDEX from Appendix 1 workbook to access this summary?

Also, can you advise on version of Excel being used?

 

[Joe4]

Duplicate: http://www.mrexcel.com/forum/showthread.php?t=544421

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).