INDEX formula to copy formula horizontally

[NJS1982]

Hi all, I have a table that looks like the below example. I want to be able to use an INDEX formula to transpose figures from this vertical data, to another (working out %) which is horizontal:

Table to INDEX:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Satisfied[/TD]
[TD]Neutral[/TD]
[TD]Dissatisfied[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]222[/TD]
[TD]13[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]172[/TD]
[TD]10[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Q3[/TD]
[TD]82[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Q4[/TD]
[TD]107[/TD]
[TD]7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Totals of each[/TD]
[TD]583[/TD]
[TD]36[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]Overall Total[/TD]
[TD]648[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table to transpose to:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]Satisfied %
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Neutral %[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dissatisfied %[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am basically trying to work out percentages, for a much larger table that I am working on, and wish to copy the formula from left to right on each row of the blank cells int he transposed table.

So, think it is something like =INDEX(first table array), then look up 'Satisfied' and look at column 2, row 2. If I drag that formula right, I want it to look at the same array, but use the figure from column 2, row 3. Then column 2, row 4 etc.

 

[Special-K99]

Cant you just copy from top left to the right hand Q4 cell
and paste Special selecting Transpose thereby doing it in one movement?

Its just copied the order and cells perfectly for me.

 

[Fluff]

As long as the row & column headers match in each table you can use

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Satisfied	Neutral	Dissatisfied					Q1	Q2	Q3	Q4
2	Q1	222	13	10				Satisfied	222	172	82	107
3	Q2	172	10	8				Neutral	13	10	6	7
4	Q3	82	6	7				Dissatisfied	10	8	7	4
5	Q4	107	7	4
6	Totals of each	583	36	29
7	Overall Total	648
Data

Cell Formulas
Range	Formula
I2	=INDEX($B$2:$D$5,MATCH(I$1,$A$2:$A$5,0),MATCH($H2,$B$1:$D$1,0))

 

[tyija1995]

Hey,

Something like this?

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Satisfied[/TD]
[TD]Neutral[/TD]
[TD]Dissatisfied[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Q1[/TD]
[TD]222[/TD]
[TD]13[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Q2[/TD]
[TD]172[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Q3[/TD]
[TD]82[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Q4[/TD]
[TD]107[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Totals of each[/TD]
[TD]583[/TD]
[TD]36[/TD]
[TD]29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Overall Total[/TD]
[TD]648[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Satisfied %[/TD]
[TD]38.08%[/TD]
[TD]29.50%[/TD]
[TD]14.07%[/TD]
[TD]18.35%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Neutral %[/TD]
[TD]36.11%[/TD]
[TD]27.78%[/TD]
[TD]16.67%[/TD]
[TD]19.44%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Dissatisfied %[/TD]
[TD]34.48%[/TD]
[TD]27.59%[/TD]
[TD]24.14%[/TD]
[TD]13.79%[/TD]
[/TR]
</tbody>[/TABLE]


Where B10 holds the formula copied across to D12 (in this example):

B10:

INDEX($A$1:$D$7,MATCH(B$9,$A$1:$A$7,FALSE),MATCH(LEFT($A10,LEN(TRIM($A10))-2),$A$1:$D$1,FALSE))/INDEX($B$1:$D$7,6,MATCH(LEFT($A10,LEN(TRIM($A10))-2),$B$1:$D$1,FALSE))

Each row will add up to 100% (B10:E10), (B11:E11), (B12:E12) as you would expect.