Need a formula to populate figures from other cells

[Goddess]

Hi,

I have a table here, where members will key in the data (C4:D5) in the "Data Sheet" column. I will then need to populate the figures into the "Summary" column (H5:K7). Is there any formula to do this, or manually key in the cell references as what I'm doing now.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Data Sheet						Summary
2
3	Group1		Apples	Orange				Apples		Orange
4		Asia	1	3				Asia	Europe	Asia	Europe
5		Europe	1	2			Group1	1	1	3	2
6		Total	2	5			Group2	4	5	5	8
7							Group3	1	5	5	6
8	Group2		Apples	Orange			Total	6	11	13	16
9		Asia	4	5
10		Europe	5	8
11		Total	9	13
12
13	Group3		Apples	Orange
14		Asia	1	5
15		Europe	5	6
16		Total	6	11
Sheet1

Cell Formulas
Range	Formula
H5	H5	=C4
I5	I5	=C5
J5	J5	=D4
K5	K5	=D5
H6	H6	=C9
I6	I6	=C10
J6,I7	J6	=D14
K6	K6	=D10
H7	H7	=C14
J7	J7	=D14
K7	K7	=D15
H8:K8	H8	=SUM(H5:H7)
C16:D16,C11:D11,C6:D6	C6	=SUM(C4:C5)

Thanks!

 

[kweaver]

Not sure I really like my alternative, but I think this will work with formulas.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Data Sheet						Summary
2
3	Group1		Apples	Orange				Apples		Orange
4		Asia	1	3				Asia	Europe	Asia	Europe
5		Europe	1	2			Group1	1	1	3	2
6		Total	2	5			Group2	4	5	5	8
7							Group3	1	5	5	6
8	Group2		Apples	Orange			Total	6	11	13	16
9		Asia	4	5
10		Europe	5	8
11		Total	9	13
12
13	Group3		Apples	Orange
14		Asia	1	5
15		Europe	5	6
16		Total	6	11
Sheet1

Cell Formulas
Range	Formula
H5:H7	H5	=INDEX($B$1:$D$16,3+MATCH($G5,$A$3:$A$16,0),COLUMN()-6)
I5:I7	I5	=INDEX($B$1:$D$16,4+MATCH($G5,$A$3:$A$16,0),COLUMN()-7)
J5:J7	J5	=INDEX($B$1:$D$16,3+MATCH($G5,$A$3:$A$16,0),COLUMN()-7)
K5:K7	K5	=INDEX($B$1:$D$16,4+MATCH($G5,$A$3:$A$16,0),COLUMN()-8)
H8:K8	H8	=SUM(H5:H7)

 

[Goddess]

Not sure I really like my alternative, but I think this will work with formulas.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Data Sheet						Summary
2
3	Group1		Apples	Orange				Apples		Orange
4		Asia	1	3				Asia	Europe	Asia	Europe
5		Europe	1	2			Group1	1	1	3	2
6		Total	2	5			Group2	4	5	5	8
7							Group3	1	5	5	6
8	Group2		Apples	Orange			Total	6	11	13	16
9		Asia	4	5
10		Europe	5	8
11		Total	9	13
12
13	Group3		Apples	Orange
14		Asia	1	5
15		Europe	5	6
16		Total	6	11
Sheet1

Cell Formulas
Range	Formula
H5:H7	H5	=INDEX($B$1:$D$16,3+MATCH($G5,$A$3:$A$16,0),COLUMN()-6)
I5:I7	I5	=INDEX($B$1:$D$16,4+MATCH($G5,$A$3:$A$16,0),COLUMN()-7)
J5:J7	J5	=INDEX($B$1:$D$16,3+MATCH($G5,$A$3:$A$16,0),COLUMN()-7)
K5:K7	K5	=INDEX($B$1:$D$16,4+MATCH($G5,$A$3:$A$16,0),COLUMN()-8)
H8:K8	H8	=SUM(H5:H7)

thanks!