Solved

Excel - Rows and Columns to Repeat

Posted on 2001-08-13
13
941 Views
Last Modified: 2011-10-03
I have an Excel document which contains a table with row and column headings as well as a table heading. Using 'File/Page Setup...' I have entered values under 'Print titles' for 'Rows to repeat at top:' and 'Columns to repeat at left:'. When I print the table (it is one page high by three pages wide) the row and column headings are repeated correctly on each page however the table title is truncated on all but the first page. Is there any way around this?
0
Comment
Question by:Geoffro
  • 3
  • 3
  • 2
  • +3
13 Comments
 
LVL 22

Accepted Solution

by:
ture earned 100 total points
ID: 6383156
Geoffro,

What you could do (but perhaps not what you want to do) is to increase the column width of the column that contains the tables' title so that the entire title fits within the column.

Ture Magnusson
Karlstad, Sweden
0
 
LVL 6

Expert Comment

by:bkpchs237
ID: 6386029
Geoffro,

Ensure that your title falls within the boundaries of the right-most column that you are printing.  Also, make sure that your title displays fully across the top of your spreadsheet to this column when you view your spreadsheet in normal view and in print preview.  That means to make sure that there is nothing entered in any cell to the right of the cell that holds the title that might prevent it from displaying across the desired columns.

If necessary, you may need to make your title two lines within the same row.  Accomplish this by placing your cursor at the desired point for a line break within the words within the title cell and press Alt+Enter on your keyboard and you will now have a title on two lines.

Hope this helps.
0
 

Author Comment

by:Geoffro
ID: 6386465
Thank you for your comments ture and bkpchs237 however I was already aware of the suggestions you have made. Is there a way around my problem without increasing the width of the first column or by splitting my table title over two lines?
0
 
LVL 22

Expert Comment

by:ture
ID: 6387190
Geoffro,

I'm sorry, but I don't know another way to accomplish this.
I assume that the custom header in page setup is not what you look for either.

/Ture
0
 

Expert Comment

by:Janney
ID: 6389967
Creating the title as a centered or left justified heading in Page Setup Custom Header should enable you to have the title print on each page.
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 6391916
Try to put the table title in the header instead of the
repeating rows.

If this is variable use a little macro that will be executed on print time, something like:

------------

With ActiveSheet.PageSetup
        .LeftHeader = Cells(1, 1)
End With

------------
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Geoffro
ID: 6404604
Thanks for your comment Murphey2. I really want to avoid using a macro if at all possible. I have thought of putting the table title in the header however if I do that when I view the table in Excel I won't see the title (I could move the table title into the second column but would prefer not to).

It looks as though there is no clear way of doing this without changing the appearance of the layout.
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 6405279
Geoffro,

<<however if I do that when I view the table in Excel I won't see the title >>

If you look closer to the sugested macro, you see that the Header is filled from the header that is visible on the sheet ("cells(1,1)" = actualy Cell "A1").

There is no other way to solve this, unless you will define your tabel title in 3 diferent columns (one for each page).
The displayed title on the second and third page are always the same width as the first column on that page.

GL
0
 

Expert Comment

by:amp072397
ID: 6709160
Geoffro, please reply to the comments here.

amp
Community Support Moderator
0
 

Expert Comment

by:Janney
ID: 6709272
Another option might be to put the title in the Page Header and also have it on the Excel spreadsheet.  Avoid having it print twice by using the Set Print Area command to select only the tables you want to print.  

That way the title would print correctly and also be visible when you are working in your spreadsheet.
0
 

Expert Comment

by:amp072397
ID: 6725261
Okay, experts. Who do I award points to? I appreciate any input.

amp
community support moderator
0
 

Expert Comment

by:Janney
ID: 6725674
I thought Murphey2's suggestion of a little macro was the "cleanest" way to achieve Geoffro's priority of keeping the title visible on the worksheet while printing it on each page of the report.  However, putting it in the page header would be the simplest and probably most appropriate.
0
 

Expert Comment

by:amp072397
ID: 6727147
The question was "Is there any way around this?" The asker is not using Excel's features properly. When someone *must* have their page heading/report title on-screen, create a textbox with the page heading and increase the height of row 1 to accomodate it.

Asker states:

>It looks as though there is no clear way of doing this without changing the appearance of the layout.

The statement is somewhat unfair in that I don't know of any program where you can "change the appearance" without "changing the appearance of the layout".

Splitting points between ture and bkpchs237. Hope everyone is agreeable.

Points on this question reduced to 100 and awarded to ture.

bkpchs237, see this question for your points: http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20254169 Just post as answer at that question and I will accept.

amp
community support moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Outlook Free & Paid Tools
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now