Copy a Column But Paste it as a transpose and link together?

  • Thread starter Thread starter Gunjani
  • Start date Start date
G

Gunjani

How may I copy a column from worksheet 1 then transpose and paste link
together on worksheet 2? Using paste special I can only manage to do one
or the other.
 
Think it's not possible to paste transpose and link via paste special,
but we can use TRANSPOSE()

Perhaps try the example set-up below to get the hang of using TRANSPOSE()?

Assuming the source range
is in Sheet1, in A1:A3 (ie a 3R x 1C grid)

In Sheet2
------------
Select the destination range for the tranpose
say A1:C3 (destination range must be the "converse", i.e. a 1R x 3C grid)

With A1:C3 selected,
put in the *formula bar*: =TRANSPOSE(Sheet1!A1:A3)

Array-enter the formula, i.e.:
Hold down CTRL + SHIFT, press ENTER
(instead of just pressing ENTER)

Done correctly, Execl will wrap curly braces around the formula,
viz.:{=TRANSPOSE(Sheet1!A1:A3)}

Don't type-in the curly braces yourself!

The same formula {=TRANSPOSE(Sheet1!A1:A3)}will appear
in each of the cells in A1:C3

Now, any changes in the source range A1:A3 in Sheet1 will be auto-reflected
(i.e. "linked") in the transposed destination range A1:C3 in Sheet2
 
Think it's not possible to paste transpose and link via paste special,
but we can use TRANSPOSE()

Perhaps try the example set-up below to get the hang of using TRANSPOSE()?

Assuming the source range
is in Sheet1, in A1:A3 (ie a 3R x 1C grid)

In Sheet2
------------
Select the destination range for the tranpose
say A1:C3 (destination range must be the "converse", i.e. a 1R x 3C grid)

With A1:C3 selected,
put in the *formula bar*: =TRANSPOSE(Sheet1!A1:A3)

Array-enter the formula, i.e.:
Hold down CTRL + SHIFT, press ENTER
(instead of just pressing ENTER)

Done correctly, Execl will wrap curly braces around the formula,
viz.:{=TRANSPOSE(Sheet1!A1:A3)}

Don't type-in the curly braces yourself!

The same formula {=TRANSPOSE(Sheet1!A1:A3)}will appear
in each of the cells in A1:C3

Now, any changes in the source range A1:A3 in Sheet1 will be auto-reflected
(i.e. "linked") in the transposed destination range A1:C3 in Sheet2
Click to expand...

Thanks Max,
Just couple of questions
1)In layman's term what is an array, why the significance CTRL + SHIFT,
press ENTER
2)Can you assist with the following:

A formatted cell [hh]:min has this formula

=SUM(C4:BB4,AX25,AY25)

Cells C4:BB4 is formatted [hh]:min, while Cells AX25 & AY 25 are
formatted 'General' but represent no.of hours i.e 14 as 14 hrs

How may I acheive the correct summed hours, prefer answer format in
[hh]:mm please
 
Gunjani said:
Thanks Max,
Click to expand...

You're welcome ..
Just couple of questions
1)In layman's term what is an array, why the significance CTRL + SHIFT,
press ENTER
Click to expand...

Perhaps you could check up Excel's Help for more info on arrays

Click Help > Contents & Index > Index tab > Type: arrays

Array formulas require that you press CTRL+SHIFT+ENTER,
instead of just ENTER, to enter the formula
2)Can you assist with the following:

A formatted cell [hh]:min has this formula

=SUM(C4:BB4,AX25,AY25)

Cells C4:BB4 is formatted [hh]:min, while Cells AX25 & AY 25 are
formatted 'General' but represent no.of hours i.e 14 as 14 hrs

How may I acheive the correct summed hours, prefer answer format in
[hh]:mm please
Click to expand...

The number "1" in Excel's time format is equiv. to 1 day (i.e = 24 hours)

So one quick way is just to divide the numbers in AX25 & AY25 by 24,
viz.: =SUM(C4:BB4,AX25/24,AY25/24)
 
Gunjani said:
Thanks Max,
Click to expand...

You're welcome ..
Just couple of questions
1)In layman's term what is an array, why the significance CTRL + SHIFT,
press ENTER
Click to expand...

Perhaps you could check up Excel's Help for more info on arrays

Click Help > Contents & Index > Index tab > Type: arrays

Array formulas require that you press CTRL+SHIFT+ENTER,
instead of just ENTER, to enter the formula
2)Can you assist with the following:

A formatted cell [hh]:min has this formula

=SUM(C4:BB4,AX25,AY25)

Cells C4:BB4 is formatted [hh]:min, while Cells AX25 & AY 25 are
formatted 'General' but represent no.of hours i.e 14 as 14 hrs

How may I acheive the correct summed hours, prefer answer format in
[hh]:mm please
Click to expand...

The number "1" in Excel's time format is equiv. to 1 day (i.e = 24 hours)

So one quick way is just to divide the numbers in AX25 & AY25 by 24,
viz.: =SUM(C4:BB4,AX25/24,AY25/24)
 
Back
Top