Help! Using excel 2010 starter

[Alpacino]

Hi everyone, I'm struggling to do a summary table using info from a table. The table has lists going down column A e.g. Row 1 (Sales), row 2(waste), row 3 (wages). Column 2 and so on has wk numbers in them with their actual figures in cells e.g. Wk 1 sales 45k wk 2 46k. What I would like to do is create a summary like vlookup??? to select Kpi and wk nber to give me the cell data. Anyone help??
Thanks in advance
Alpacino

 

[Jon von der Heyden]

Hi

Here's one way:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		1			2			3
2	KPI	Wk Bud	Wk Act	RAG	Wk Bud	Wk Act	RAG	Wk Bud	Wk Act	RAG
3	Sales	765086	725063	R	759455	720507	R			R
4	LFL	2	-4.49	R	2	-10.89	R	2		R
5	Waste	9674	9906	R	9663		G			R
6
7
8	Choose week	2
9	Choose KPI	Sales
10
11	Budget	759455
12	Actual	720507
13	RAG	R
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B11	=INDEX($B$3:$J$5,MATCH($B$9,$A$3:$A$5,0),MATCH($B$8,$B$1:$J$1,0)+ROWS($A$11:$A11)-1)

Formula in B11 copied down through to B13.

Note, week numbers in row 1 are not within merged cells. I have used 'centre across selection' in my example so that they appear centred over the respective categories.

 

[Alpacino]

Hi Jon
Thanks again for your help. It doesn't seem to copying down well just copies the same result.
I mist be doing something wrong
How do you do centre across section?

 

[Jon von der Heyden]

For example, select B1:D1
Go to Format Cells (Control+1)
Go to the Alignment tab
In the 'Horizontal' drop-down box choose 'Centre across selection'

 

[Alpacino]

Ive done that still not working.
Could u please copy the formula for B12 and B13
Thank you

 

[Jon von der Heyden]

B12: =INDEX($B$3:$J$5,MATCH($B$9,$A$3:$A$5,0),MATCH($B$8,$B$1:$J$1,0)+ROWS($A$11:$A12)-1)

B13: =INDEX($B$3:$J$5,MATCH($B$9,$A$3:$A$5,0),MATCH($B$8,$B$1:$J$1,0)+ROWS($A$11:$A13)-1)

Is your table set-up in the same way as my example?

 

[Alpacino]

It is just that the summary bit is the top of page.
regards Alan

 

[Jon von der Heyden]

Hi Alan

I really can't diagnose the problem without knowing the specific layout of your table. Can you post another sample please, and also let me know the column indexes and row numbers?

 

[Jon von der Heyden]

Does this help?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Choose week	2
2	Choose KPI	Sales
3
4	Budget	759455
5	Actual	720507
6	RAG	R
7
8
9		1			2			3
10	KPI	Wk Bud	Wk Act	RAG	Wk Bud	Wk Act	RAG	Wk Bud	Wk Act	RAG
11	Sales	765086	725063	R	759455	720507	R			R
12	LFL	2	-4.49	R	2	-10.89	R	2		R
13	Waste	9674	9906	R	9663		G			R
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B4	=INDEX($B$11:$J$13,MATCH($B$2,$A$11:$A$13,0),MATCH($B$1,$B$9:$J$9,0)+ROWS($A$4:$A4)-1)
B5	=INDEX($B$11:$J$13,MATCH($B$2,$A$11:$A$13,0),MATCH($B$1,$B$9:$J$9,0)+ROWS($A$4:$A5)-1)
B6	=INDEX($B$11:$J$13,MATCH($B$2,$A$11:$A$13,0),MATCH($B$1,$B$9:$J$9,0)+ROWS($A$4:$A6)-1)

 

[Alpacino]

That's a great help.
Been very kind in sharing your knowledge

 

[Alpacino]

La question could I move the summary bit to a different sheet???