Paste Special - Values - Transpose

[Guest]

If I have data in columns per below:

A B C D
1/1/05 1/2/05 1/3/05 1/4/05

And I want the end result to be the below.

Row 1 1/1/05
Row 2 1/2/05
Row 3 1/3/05
Row 4 1/4/05

But as additional data is added, for example in column E (1/5/05), I want to
be able to copy the reference in row 4 down and have Row 5 reference what's
in column E and so on.

If I copy the reference in row 4, the reference moves down a row instead of
over a column. I know I can use paste values - transpose, but is there a way
to do this if I want to use and update cell references? Just as background,
I'm pulling data from another worksheet, which has to be formatted with the
dates across the top, but in my worksheet, I want the dates in one column,
but I want to reference what's being entered in the other worksheet.

Any help is appreciated.

 

[SteveG]

Use the TRANSPOSE and IF functions.

I don't know how many columns out you will go but I did 10 (A1:J1).

If your list begins in A1 of the new sheet then select cells A1:A10.
Type

=IF(TRANSPOSE(Sheet Name A1:J1)=0,"",TRANSPOSE(Sheet Name A1:J1))

Commit to the function using Ctrl-Shift-Enter as it is an arra
formula. It should have curly brackets around it after you do.

This will return the value of the cells that contain any data in th
row of the source sheet in list format from cells A1:A10. If there i
no data the cells in your list will stay blank until information i
added to A1:J1 of your source sheet's row.



HTH

Cheers,

Stev

 

[SteveG]

You can also do this using a range like Sheet!2A1:J15 as your source.
You just need to remember to select the same number of columns for the
rows and rows for your columns in your new list. So you would select
10 rows (A:J in the original data) and 15 columns(1:15 in the original
data) where you want the data transposed. This way it is a one time
deal entering in the formula for the entire range of data. If your
range expands, it is easy to edit.



Cheers,

Steve