using the transpose() function in a different way

[martha555]

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.

 

[Peter_SSs]

I then add my own data below row 3.

In which sheet, a.xls or b.xls?

 

[martha555]

hi thanx for the response. In file b.xls.

 

[Peter_SSs]

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.