Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can I copy a row to a column?
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 |
#2
|
|||
|
|||
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. -- 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" wrote in message ... 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 |
#3
|
|||
|
|||
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 "Ken Wright" wrote in message ... 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. -- 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" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote in message ... 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 <snip |
#5
|
|||
|
|||
I guess you _are_ right (DOH). Sorry to make you type it all out before I
got it. Many Thanks, Dennis "Ken Wright" wrote in message ... 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" wrote in message ... 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 <snip |
#6
|
|||
|
|||
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" wrote in message ... I guess you _are_ right (DOH). Sorry to make you type it all out before I got it. Many Thanks, <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Copy a column in worksheet with a character change | Excel Worksheet Functions | |||
How do you copy a cell formula down a column without displaying n. | Excel Worksheet Functions | |||
copy COLUMN from 1 worksheet to another (in a different workbook) | Excel Discussion (Misc queries) | |||
how to copy 2350 hyperlink full paths to any column in a worksheet ? | Excel Discussion (Misc queries) |