Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel - Workbook page numbering


(!)

EDD32's Avatar
EDD32 EDD32 is offline
Member with 64 posts.
THREAD STARTER
 
Join Date: May 2003
10-Mar-2006, 12:31 PM #1
Excel - Workbook page numbering
hi all,
I have an excel workbook with several worksheets, each a page a long (i dont want them all merge in one sheet). I need to number them sequentially as they appear on the workbook.
The only way i can number them now is by going into each worksheet and inserting a page footer and enter a number manually !

Is there a way whereby all worksheets/pages are numbered automatically and more importantly page numbers changes if a new sheet is inserted ?


Thank u kindly.
Edd.
The Villan's Avatar
Member with 2,170 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
Experience: Advanced at times
11-Mar-2006, 12:13 PM #2
The way that I would approach this is to use Report Manager

Depending on which Excel version you have Report manager may or may not be in the view menu tree. 2002 and above doesn't come with the app. You need to download it and fire it up. Once fired up you can then load it into excel by going to Tools Addin and selecting report manager. Once installed, you can find it under the View menu.

Using the Excel 2002 Report Manager add-in

Using the Report Manager add-in program, you can combine worksheets (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.), views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.), and scenarios (scenario: A named set of input values that you can substitute in a worksheet model.) into printable reports. For example, if you have a Best Case and a Worst case scenario, a Summary view, and a Details view, you can create a report that presents the Best Case scenario in the Details view and another report that presents the Best Case Scenario in the Summary view. Reports that you create are automatically saved with your workbook so that you can print them at any time.

The Excel 2002 Report Manager add-in is not included with Excel 2002, as was the case with previous versions of Excel. To use it, you must first download the Excel 2002 Report Manager add-in from Downloads on Microsoft Office Online. Follow the instructions on the Downloads page to install the Report Manager. http://office.microsoft.com/search/r...HA010346581033

Note If the Report Manager is not available on the View menu after downloading the add-in, click Add-ins on the Tools menu, and then select Report Manager.

Create a report for printing

On the View menu, click Report Manager.
Click Add.
In the Report Name box, type a name for the report.
In the Sheet box , click the sheet you want to use for the first section of the report.
Do one of the following:
To use a view for the first section of the report, select the View check box, and then click the view in the View box.
To use a scenario for the first section of the report, select the Scenario check box, and then click the scenario in the Scenario box.
Click Add to enter the view or scenario as a section in the Sections in this Report box.
Repeat step 5 and 6 until you've created all of the sections you want in the report.
To change the order of the sections, in the Sections in this report box, click the section you want to move, and then click either Move Up or Move Down.
To number the pages of the report consecutively, select the Use Continuous Page Numbers check box.
Note Microsoft Excel prints sections of a report in the order in which they're listed in the Sections in this report box.

Edit a report for printing

On the View menu, click Report Manager.
In the Reports box , click the report you want to edit, and then click Edit.
Do one or more of the following:
To add a new section, click the sheet, view, and scenario you want under Section to Add, and then click Add.
To delete a section, click the section in the Sections in this report box, and then click Delete.

To change the order of the sections, in the Sections in this report box, click the section you want to move, and then click either Move Up or Move Down.

To number the pages of the report consecutively, select the Use Continuous Page Numbers check box.

Note Microsoft Excel prints sections of a report in the order in which they're listed in the Sections in this report box.



Sorry a bit long winded but it is an advanced concept, unfortunately not used by many people.

You may want to read up on it before attempting - it really depends on what level of Excel knwoledge you have.

Get back to me if you need further help when you have installed Report Manager
EDD32's Avatar
EDD32 EDD32 is offline
Member with 64 posts.
THREAD STARTER
 
Join Date: May 2003
11-Mar-2006, 08:55 PM #3
Thx Villan for this !
Firstly, well the report generation works fine but it does print the page numbers whn the report get printed. i have selected the box 'Use Continiuos page numbering' but no cigar !
any ideas there ?
Secondly, in the 'View' option the list menu doesnt show any option expect '(none)' what does this View option exactly involve/used for?

many thanks.
Edd
The Villan's Avatar
Member with 2,170 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
Experience: Advanced at times
12-Mar-2006, 01:13 AM #4
Quote:
Originally Posted by EDD32
Thx Villan for this !
but no cigar !
any ideas there ?
Secondly, in the 'View' option the list menu doesnt show any option expect '(none)' what does this View option exactly involve/used for?
many thanks.
Edd
Edd - not sure what you mean by "but no cigar!" - am I being dim there?

View
There is an option on the view menu that says Custom Views.
Basically you create range names for different areas of your workbook. You can then use the range names in the custom views. Once you have done that, you can use those views in the report manager.
I used to use range names all the time in my workbooks to help me identify different areas of my spreadsheets. e.g.
InputArea1
InputArea2
PrintArea1
PrintArea2
PrintArea3

PrintArea1/2/3 could be the same range on the spreadsheet but with different setups e.g.

PrintArea1 may have hidden rows and columns.
PrintArea2 may only have the first ten rows and 6 columns of the range
PrintArea3 may be the complete range

So its basically setting up a sort of view template/master for each range name that you create. You no longer have to go through the b**** aching job of changing everything all the time. Obviously, not all range names would be used in the custom views.

Once you have your range names included in the custom views, you can use them very effectively. e.g.
If somebody wants to input into InputArea1 you just go into Custom view and select the view name and show and click on OK - Excel will show the view that you want at that time.

The real big spin off is when you use these view names for macro work.

Anyway, moving on - Once the various views have been setup, some or all of the view names can be used in the report manager. Thats where good range naming come in. I always used Print to start a range/view name so that I would know which ones to include in my report manager.

I do hope that the above makes sense - as the use of report manager and custom views and range names opens a big can of worms, but once setup, can save one enormous amount of work afterwards both manually or with the use of macros. Its all about planning the design of your workbooks before you actually create the workbook.

Anyway, if I have bamboozled you just keep coming back and asking. Take it one step at a time and just keep firing the questions. Thats what all of us are here for :-)

Last edited by The Villan; 12-Mar-2006 at 01:20 AM..
The Villan's Avatar
Member with 2,170 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
Experience: Advanced at times
12-Mar-2006, 02:35 AM #5
Edd
If you have read the above and digested so to speak. This is how I would set about your page numbering issue.

1. go to sheet1 in your workbook
2. View headers and footers and in the custom footer insert the page and page numbers icons as you would do normally
3. Once you have done that click on OK and come back into the spreadsheet
4. Select the file menu and then select Page Setup - make sure that on the Page Tab the first page number is set as Auto - if it isn't change it by typing Auto in - then click on OK
5. Select the range that you want to print.
6. Select the View menu and then select Custom View - select Add and type a name in for this area that you want to print, making sure that you turn on the option for Include in View Print Settings (the other option depends on if you need it) - click on OK

You have set up your first view.

Now do the same thing for sheet2 by following above instructions

repeat for all other sheets.

Once you have set up all the views

Select the View menu and then select Report Manager
1. Click on add and type a name for the report - such as EddReport1
2. In the section to add - select the spreadsheet you want for your first page to print - then select the correct view - then click on add
3. repeat that for the other sheets you want in the report
4. Make sure the use continuous page numbers are turned on
5. Click on OK

Your report is now setup and you can print it whenever you want. If you have to print 6 of the same report, you can tell excel to print 6 reports.

If you later on create a new sheet, you go through the above process to bring it into that report only you use the edit option to add the new sheet in. You will also be able to move the new page into the order you want it to be printed.

My suggestion is that you practice first by creating a brand new workbook and putting a small amount of data in each of the three sheets (say a block of numbers from A1 to C6 in each sheet) and then set up the views and report manager and test the printing.

That way, if you **** it up, you haven't wasted too much time and printing. Once you are clear on how to do it, then set it up with your live workbook and as a temporary situation save it a s a different file name until you are happy that it works.

Hope all of that helps. As I said before - come back if you have any problems

Cheers
Les
jim4004's Avatar
jim4004 jim4004 is offline
Member with 92 posts.
 
Join Date: Feb 2006
Location: USA
Experience: Advanced
12-Mar-2006, 11:06 AM #6
Select All of the Sheets and then print
I always just select all of the sheets I want to print. Click on the sheet name at the bottom and then hold the shift key down and click on the other sheets. You can also use CTL + Shift to select random sheets.

This will print all of the sheets as one, BUT REMEMBER when you have multiple sheets selected everything you do to the sheets will be done to all of them - so turn it off when you're done!

It's also handy for setting up headers and footers on all sheets. Just remember to turn it off.
The Villan's Avatar
Member with 2,170 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
Experience: Advanced at times
12-Mar-2006, 12:57 PM #7
Thats one of the dangers of doing it your way Jim, is overwriting things in each sheet, and making sure that you don't make mistakes each time you do it. Its all got to do with spreadsheet integrity. I think it was quoted somewhere that over 80% of spreadsheets have errors in them and cannot be relied upon.

My way is a more permanent approach and is less likely that human errors will occur.
jim4004's Avatar
jim4004 jim4004 is offline
Member with 92 posts.
 
Join Date: Feb 2006
Location: USA
Experience: Advanced
12-Mar-2006, 04:06 PM #8
Select Spreadsheets vs. Add-Ons
Edd, I guess you'll have to balance the DANGER of selecting multiple spreadsheets with the slim chance that the another user having to use your spreadsheet will have add-ons installed.
The Villan's Avatar
Member with 2,170 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
Experience: Advanced at times
12-Mar-2006, 04:13 PM #9
I'm out of here. :-)
jim4004's Avatar
jim4004 jim4004 is offline
Member with 92 posts.
 
Join Date: Feb 2006
Location: USA
Experience: Advanced
14-Mar-2006, 01:59 PM #10
Report Manager - More elegant solution
I looked into the Report Manager after our discussion, and it is an effective way to manage your sheets. Your detailed description of the steps, and the links was excellant.

Thanks,

Jim
The Villan's Avatar
Member with 2,170 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
Experience: Advanced at times
14-Mar-2006, 02:09 PM #11
Well thats kind of you to say that Jim. Thankyou.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑