using the transpose() function in a different way

martha555

New Member
Joined
Jun 28, 2005
Messages
41
hi :) ,
I have two files, a.xls and b.xls.
The contents of a.xls are as follows:
A B C
1 aa 1 a
2 bb 2 b
3 cc 3 c

I want to copy the the entire contents of the rows from file a to file b as columns, i.e., file b.xls should look as follows:

A B C
1 aa bb cc
2 1 2 3
3 a b c

I then add my own data below row 3. Using the transpose function does the needful if the number of rows from which data needs to be transferred is known in advance. My problem is:
1. I may insert more rows in file a, which can also be inbetween, i.e., between rows 1 & 2 including blank rows. I want to reflect these changes in file b by inserting a column at the relevant place, so that the data which I have added does not get affected.

2. I want to update the file(b), whenever I open it or whenever I make changes to file a.

Is this possible? Please help. :rolleyes:
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows
martha555

I am not sure if I have understood this correctly. However, you could try something like this in b.xls.

In cell A1 put the formula:
=IF(TRANSPOSE('C:\Mr Excel\[a.xls]Sheet1'!$A$1:$E$10)="","",TRANSPOSE('C:\Mr Excel\[a.xls]Sheet1'!$A$1:$E$10))
You may need to adjust path and/or sheet name and/or range to suit.

Select the appropriate (transposed) range in b.xls
In my example the data in a.xls is in the range A1:E10. That is a range 5 columns wide by 10 rows high so in b.xls (after I have put the above formula in A1) I select A1:J5. That is a range 10 columns wide by 5 rows high.

Press F2 and then confirm the formula to the whole range with CTRL+SHIFT+ENTER

See if that is on the right track.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,760
Messages
5,574,074
Members
412,566
Latest member
TexasTony
Top