INDEX formula to copy formula horizontally

NJS1982

Board Regular
Joined
Sep 24, 2009
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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:

SatisfiedNeutralDissatisfied
Q12221310
Q2172108
Q38267
Q410774
Totals of each5833629
Overall Total648

<tbody>
</tbody>

Table to transpose to:

Q1Q2Q3Q4
Satisfied %
Neutral %
Dissatisfied %

<tbody>
</tbody>

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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
As long as the row & column headers match in each table you can use

Book1
ABCDEFGHIJKL
1SatisfiedNeutralDissatisfiedQ1Q2Q3Q4
2Q12221310Satisfied22217282107
3Q2172108Neutral131067
4Q38267Dissatisfied10874
5Q410774
6Totals of each5833629
7Overall Total648
Data
Cell Formulas
RangeFormula
I2=INDEX($B$2:$D$5,MATCH(I$1,$A$2:$A$5,0),MATCH($H2,$B$1:$D$1,0))
 
Upvote 0
Hey,

Something like this?

ABCDE
1SatisfiedNeutralDissatisfied
2Q12221310
3Q2172108
4Q38267
5Q410774
6Totals of each5833629
7Overall Total648
8
9Q1Q2Q3Q4
10Satisfied %38.08%29.50%14.07%18.35%
11Neutral %36.11%27.78%16.67%19.44%
12Dissatisfied %34.48%27.59%24.14%13.79%

<tbody>
</tbody>


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

B10:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top