Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Columns
Hi,
In Word, it has Columns function that is very useful. When the column A is full in one page, the data automatically go to column B and then C.... ----- In Excel, I would like to create a sheet, which has 5 columns. I hope that (on column A) after data is entered into row 60, the next data will move to column B, not go to the row 61. And then when column B is entered to row 60, the next data will move to column C.... Please help. Chi |
#2
|
|||
|
|||
One play is to mimic this as an output on another sheet ..
Assume data will be entered in A1:A300 in Sheet1 In Sheet2, Put in A1: =IF(INDIRECT("'Sheet1'!A"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC T("'Sheet1'!A"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))) Copy A1 across to E1, fill down to E60 A1:E60 will return the desired outputs from the inputs in Sheet1's A1:A300 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chi" wrote in message ... Hi, In Word, it has Columns function that is very useful. When the column A is full in one page, the data automatically go to column B and then C.... ----- In Excel, I would like to create a sheet, which has 5 columns. I hope that (on column A) after data is entered into row 60, the next data will move to column B, not go to the row 61. And then when column B is entered to row 60, the next data will move to column C.... Please help. Chi |
#3
|
|||
|
|||
Thanks Max, I got it. It is wonderful!
For another case that if data are entered in A1:A300 and B1:B300 (they need go together) Ex: A B Education 2003 Data are entered in A and B columns will stop at row 60 and continue to fill in D1 and E1 columns and go on.... Please help . Thanks Chi "Max" wrote: One play is to mimic this as an output on another sheet .. Assume data will be entered in A1:A300 in Sheet1 In Sheet2, Put in A1: =IF(INDIRECT("'Sheet1'!A"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC T("'Sheet1'!A"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))) Copy A1 across to E1, fill down to E60 A1:E60 will return the desired outputs from the inputs in Sheet1's A1:A300 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chi" wrote in message ... Hi, In Word, it has Columns function that is very useful. When the column A is full in one page, the data automatically go to column B and then C.... ----- In Excel, I would like to create a sheet, which has 5 columns. I hope that (on column A) after data is entered into row 60, the next data will move to column B, not go to the row 61. And then when column B is entered to row 60, the next data will move to column C.... Please help. Chi |
#4
|
|||
|
|||
.. if data are entered in A1:A300 and B1:B300
(they need go together) Assume the above is made in Sheet1 as before In Sheet1, Put in C1: =TRIM(A1&" "&B1) Copy down to C300 In Sheet2, change the reference to point to Sheet1's col C instead of col A, i.e. Put in A1: =IF(INDIRECT("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC T("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))) Copy A1 across and fill down to E60 as before -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chi" wrote in message ... Thanks Max, I got it. It is wonderful! For another case that if data are entered in A1:A300 and B1:B300 Ex: A B Education 2003 Data are entered in A and B columns will stop at row 60 and continue to fill in D1 and E1 columns and go on.... Please help . Thanks Chi |
#5
|
|||
|
|||
Hi Max,
Again, excellent! There is a small thing that I need help in this question. Please. It returns to my designed outputs. Great! However, the data of combined field (c)appear very close. I would like to make them separately. Ex: A B C Department 1000 then it becomes Department 1000 Education 200 Education200 Test 5 Test5 I wish they could show like the format below. Department 1000 Education 200 Test 5 Please help. Thank you so much! Chi "Max" wrote: .. if data are entered in A1:A300 and B1:B300 (they need go together) Assume the above is made in Sheet1 as before In Sheet1, Put in C1: =TRIM(A1&" "&B1) Copy down to C300 In Sheet2, change the reference to point to Sheet1's col C instead of col A, i.e. Put in A1: =IF(INDIRECT("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC T("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))) Copy A1 across and fill down to E60 as before -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chi" wrote in message ... Thanks Max, I got it. It is wonderful! For another case that if data are entered in A1:A300 and B1:B300 Ex: A B Education 2003 Data are entered in A and B columns will stop at row 60 and continue to fill in D1 and E1 columns and go on.... Please help . Thanks Chi |
#6
|
|||
|
|||
My last post is mess . Please read this post. Thanks.
Chi ------------- There is a small thing that I need help in this question. Please. It returns to my designed outputs. Great! However, the data of combined field (c)appear very closely. I would like to make them separately. Ex: A B C Department 1000 Department1000 Education 200 Education200 I wish they could show like the format below.( Department is at left align and 1000 is at right align) Department 1000 Education 200 Test 5 Please help. Thank you so much! Chi "Max" wrote: .. if data are entered in A1:A300 and B1:B300 (they need go together) Assume the above is made in Sheet1 as before In Sheet1, Put in C1: =TRIM(A1&" "&B1) Copy down to C300 In Sheet2, change the reference to point to Sheet1's col C instead of col A, i.e. Put in A1: =IF(INDIRECT("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC T("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))) Copy A1 across and fill down to E60 as before -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chi" wrote in message ... Thanks Max, I got it. It is wonderful! For another case that if data are entered in A1:A300 and B1:B300 Ex: A B Education 2003 Data are entered in A and B columns will stop at row 60 and continue to fill in D1 and E1 columns and go on.... Please help . Thanks Chi |
#7
|
|||
|
|||
This slight revision might produce an acceptable result ..
In Sheet1, Put instead in C1: =A1&REPT(" ",10)&B1 Copy down to C300 In Sheet2 Use the same formulas as earlier to fill the range A1:E60 Then just select the cols A to E, and format it to: Align Right (Adapt the number "10" within REPT to suit if this number is not sufficient. The "10" means repeat 10 spaces [" "]) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chi" wrote in message ... My last post is mess . Please read this post. Thanks. Chi ------------- There is a small thing that I need help in this question. Please. It returns to my designed outputs. Great! However, the data of combined field (c)appear very closely. I would like to make them separately. Ex: A B C Department 1000 Department1000 Education 200 Education200 I wish they could show like the format below.( Department is at left align and 1000 is at right align) Department 1000 Education 200 Test 5 Please help. Thank you so much! Chi |
#8
|
|||
|
|||
In Sheet1,
Put instead in C1: =A1&REPT(" ",10)&B1 And if needed, we could also use in C1: =TRIM(A1)&REPT(" ",10)&TRIM(B1) TRIM will remove any extraneous white spaces present within the cells. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 --- |
#9
|
|||
|
|||
Max,
Excellent ! I got all. Thank you so much. Chi "Max" wrote: In Sheet1, Put instead in C1: =A1&REPT(" ",10)&B1 And if needed, we could also use in C1: =TRIM(A1)&REPT(" ",10)&TRIM(B1) TRIM will remove any extraneous white spaces present within the cells. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 --- |
#10
|
|||
|
|||
You're welcome, Chi ! Glad it helped.
Thanks for the feedback and closure .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chi" wrote in message ... Max, Excellent ! I got all. Thank you so much. Chi |
#11
|
|||
|
|||
Hi Max,
I am sorry that I still need some more help. As you know that my workbook has two sheets. Sheet1 which has column A and B, and column C1 =A1&REPT(" ",10)&B1. Sheet 2, I used this formular =IF(INDIRECT("'Sheet1'!C"&COLUMNS($A$1:A1)*60-60+ROWS($A$1:A1))=0,"",INDIREC so that the value appears from A1 to A60 and then B1 to B60€¦€¦.I like this layout. However, after I insert new row in sheet 1, the formula in Column C is not copy down to the new row. Is there a way to make the formula (in column C) automatically copy down to the new row? Sheet1 A B C DEPARTMENT 55 DEPARTMENT 55 Education Department 555 Education Department 555 Heart Department (new row) Nothing shows here English and Math 565 English and Math 565 Sheet 2 A DEPARTMENT 55 Education Department 555 Nothing show here English and Math 565 The format of the words in sheet 2 will change when I insert new row in sheet 1. Ex: The word DEPARTMENT (bold) will become DEPARTMENT (not bold) and Education Department (not bold) will become Education Department(bold) In all, I still have problems in insert new row in sheet 1 and the format in sheet 2. English is my second language, so it is really hard for me to explain the problems. I hope that it is clear enough for you to understand what I have been tried to say. Sorry for any confusions. I don't know why all numbers are messy after I posted. Thank you Chi "Max" wrote: You're welcome, Chi ! Glad it helped. Thanks for the feedback and closure .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chi" wrote in message ... Max, Excellent ! I got all. Thank you so much. Chi |
#12
|
|||
|
|||
"Chi" wrote:
.... after I insert new row in sheet 1, the formula in Column C is not copy down to the new row .. Think you need a macro to do this, for which other folks are better positioned to help you (I don't know, sorry). Ex: The word DEPARTMENT (bold) will become DEPARTMENT (not bold) and Education Department (not bold) will become Education Department(bold) Not sure what's happening here, I'm afraid .. but do note that formulas cannot "carry over" any formatting from the source data. -- Are you doing all this column re-arrangements for printing reasons ? If so, think Jim Cone has a nice Excel add-in "Side by Side" which could help you. It's available - free - upon direct request to Jim. Try this post for details on how to reach Jim: http://tinyurl.com/9dwlv -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#13
|
|||
|
|||
Max,
Thank you very much for your response. I think that Jim email address is not correct, but I will try. Again, Thanks a lot. Chi Huynh "Max" wrote: "Chi" wrote: .... after I insert new row in sheet 1, the formula in Column C is not copy down to the new row .. Think you need a macro to do this, for which other folks are better positioned to help you (I don't know, sorry). Ex: The word DEPARTMENT (bold) will become DEPARTMENT (not bold) and Education Department (not bold) will become Education Department(bold) Not sure what's happening here, I'm afraid .. but do note that formulas cannot "carry over" any formatting from the source data. -- Are you doing all this column re-arrangements for printing reasons ? If so, think Jim Cone has a nice Excel add-in "Side by Side" which could help you. It's available - free - upon direct request to Jim. Try this post for details on how to reach Jim: http://tinyurl.com/9dwlv -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#14
|
|||
|
|||
I think that Jim email address is
not correct, but I will try. Sorry about that, didn't notice that google had applied a mask, but believe the original response by Jim there indicates: XX -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chi" wrote in message ... Max, Thank you very much for your response. I think that Jim email address is not correct, but I will try. Again, Thanks a lot. Chi Huynh |
#15
|
|||
|
|||
Thanks! Max
Chi "Max" wrote: I think that Jim email address is not correct, but I will try. Sorry about that, didn't notice that google had applied a mask, but believe the original response by Jim there indicates: XX -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Chi" wrote in message ... Max, Thank you very much for your response. I think that Jim email address is not correct, but I will try. Again, Thanks a lot. Chi Huynh |
#16
|
|||
|
|||
"Chi" wrote:
Thanks! Max Chi You're welcome ! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Columns | Excel Discussion (Misc queries) | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Hidden Columns in Shared Workbooks | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) |