Columns

[Guest]

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

 

[Max]

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

 

[Guest]

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]

.. 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

--

 

[Guest]

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

 

[Guest]

My last post is mess . Please read this post. Thanks.
Chi
-------------

 

[Max]

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 [" "])

--

 

[Max]

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.

 

[Guest]

Max,

Excellent !
I got all.
Thank you so much.
Chi

 

[Max]

You're welcome, Chi ! Glad it helped.
Thanks for the feedback and closure ..

 

[Guest]

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]

:
....

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

 

[Guest]

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]

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:

(e-mail address removed)


--

 

[Guest]

Thanks! Max
Chi

Sorry about that, didn't notice that google had applied a mask,
but believe the original response by Jim there indicates:

(e-mail address removed)

 

[Max]

Thanks! Max
Chi

You're welcome !