Can I copy a row to a column?

  • Thread starter Thread starter Dennis Harrelson
  • Start date Start date
D

Dennis Harrelson

Hello, Folks
I need to have a column of values on one sheet equal a row of values on a
different sheet. i.e.: 'sheet2'A1='sheet1'A1; 'sheet2'A2='sheet1'B1;
'sheet2'A3='sheet1'C1; etc. Is there a way to do this using edit/fill or
copy? It's way too tedious to type every cell in, or even to click from one
sheet to the other for every cell, but I can't find any short cut.
TIA,
Dennis
 
On the sheet you want your column. Initially create a row that links
straight back to the source data, but don't have this row cross the column
you want to put your data in. So what you have now is simply a row that
equals the other row. Select the range, edit / replace, replace = with %%.
Copy range, paste special / transpose to where you want your column, select
data in column, edit / replace, replace %% with =.

Delete the helper range.
 
Thanks, Ken.
That looks like it will work, but I guess I understated the problem. The
values in the original row which I need to copy to 'sheet2'columnA are the
sums of their respective columns, which are in turn carried from yet a third
sheet. So I need to have 'sheet1'columnA='sheet2'row1, which will vary
depending on input on 'sheet3'. As an added complication, the values
on'sheet3' are not directly inputed, but derived from formulae on that
sheet.
Thanks again,
Dennis
 
I could well be missing something here (It is getting late), but I'm pretty
sure it will do exactly what you want. Just to clarify though:-

You have for example on sheet 2 in cells
B1 - =SUM(B2:B1000)
C1 - =SUM(C2:C1000)
D1 - =SUM(D2:D1000)

and the data in the ranges above are coming from other formulas puling from
sheet 3.

What you need to do is end up on sheet 1 with the following:-
B1 - =Sheet2!B1
B2 - =Sheet2!C1
B3 - =Sheet2!D1
B4 - =Sheet2!E1
etc

so doing as I suggested and on sheet 1 in say D10, put =Sheet2!B1 and copy
across the row and you will end up with
D10 - =Sheet2!B1
E10 - =Sheet2!C1
F10 - =Sheet2!D1
G10 - =Sheet2!E1
etc

Then do the edit / replace bit so you get
D10 - %%Sheet2!B1
E10 - %%Sheet2!C1
F10 - %%Sheet2!D1
G10 - %%Sheet2!E1
etc

Copy and paste special transpose to say cell B1 on sheet 1 and you end up
with
B1 - %%Sheet2!B1
B2 - %%Sheet2!C1
B3 - %%Sheet2!D1
B4 - %%Sheet2!E1

Then edit / replace again to give you what you finally wanted
B1 - =Sheet2!B1
B2 - =Sheet2!C1
B3 - =Sheet2!D1
B4 - =Sheet2!E1
etc

then delete the data in D10:xx10

As data is updated on sheet 3 it will in turn update your sums on sheet 2
and the formulas on sheet1 will pull the sums from sheet2.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

Dennis Harrelson said:
Thanks, Ken.
That looks like it will work, but I guess I understated the problem. The
values in the original row which I need to copy to 'sheet2'columnA are the
sums of their respective columns, which are in turn carried from yet a third
sheet. So I need to have 'sheet1'columnA='sheet2'row1, which will vary
depending on input on 'sheet3'. As an added complication, the values
on'sheet3' are not directly inputed, but derived from formulae on that
sheet.
Thanks again,
Dennis
Click to expand...
<snip>
 
I guess you _are_ right (DOH). Sorry to make you type it all out before I
got it.
Many Thanks,
Dennis
 
LOL - No problem, and hopefully the step by step helps someone else out

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

Dennis Harrelson said:
I guess you _are_ right (DOH). Sorry to make you type it all out before I
got it.
Many Thanks,
Click to expand...
<snip>
 
Back
Top