Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-18-2009, 04:30 AM
Tom Tom is offline
Novice
 
Join Date: Apr 2009
Location: The Hills of West Virginia
Posts: 2
Tom is on a distinguished road
Unhappy Multiple Headers in Same Worksheet

I understand fully how to create different headers in the same Word document... and I want to achieve that same effect in Excel (I'm using Excel 2007).

In other words, I have No Header on 1st page (did that successfully, simple enough), followed by 9 pages or with a lovely header... Now I need the next several pages to be identical, except for one variable. And I'll need to repeat that several times throughout the same worksheet.

Is there a way I can do that? Thanks

Last edited by Tom; 04-18-2009 at 01:06 PM. Reason: Growing distress
Reply With Quote
  #2  
Old 04-23-2009, 02:51 PM
zyzzyva57 zyzzyva57 is offline Windows 7 32bit Office 2007
Expert
 
Join Date: Mar 2009
Location: Dawsonville, Ga (NE of Atl)
Posts: 413
zyzzyva57 is on a distinguished road
Default

Is this what you mean-1?

2?
Reply With Quote
  #3  
Old 04-24-2009, 04:36 AM
Tom Tom is offline
Novice
 
Join Date: Apr 2009
Location: The Hills of West Virginia
Posts: 2
Tom is on a distinguished road
Default

Thank you, zyzzyva57, for replying.

Ok, as usual, I see that the question I posed was about as clear as mud. Anyway, no, the videos you directed me to were helpful, but not what I wanted.

The good news is: I figured out how to do what I wanted! My question should have been Since I already know how to create headers in Excel, how can I get a portion of the header to be dynamic, changing as needed throughout the printed document? So here it is:

How to Create Dynamic Headers in Excel

Here's the end result you want: You want a cover page, and there are three sections of this document once printed. You will also want page numbers, and different names to each of the different sections -- you can think of it like chapter titles. Oh, also, you will want row headers repeated not only on the printed page, but as you are working on the PC as well. This will make it easy to keep track of what column you are in since the column headers will always be visible. Same for the reader: once printed, this document will be easy to read because column headers are repeated on every page.
The key thing to understand is that each section of the document will be on a separate worksheet within the same Excel document.
So, let's say you already have content on a worksheet. On this worksheet, you already have your document's title page, and you have the first several pages of content. Let's start by changing Sheet1 to Inventory Status. Then...
  1. Press Ctrl+Home so that you are at the upper left most cell in the worksheet.
  2. Go to the Insert menu, and select Header & Footer. The Page Setup window will appear, and you should be on the Header/Footer tab.
  3. Select the Different First Page checkbox. (This will ensure your header and footer do not appear on your title page.)
  4. Click the Custom Header... button. Your focus will then be on the Header tab of the new Header window, but notice that you have the option of creating a First Page Header that will be different from the rest. Just FYI.)
  5. You have three sections of the header that you can use: the Left section, Center section and Right section. In the Left section, put your name of the document, "Reporting System". (I'm just making this stuff up, but I want to use actual sample content for this illustration.) You can manipulate your font size, color, style, etc., using the tool buttons.
    In the Center section -- and this is where we'll put the dynamic content -- you'll type "Current Inventory" because this whole document is about the current inventory of your reporting system; BUT... there are three different parts of the reporting system you are documenting, hence the need for dynamic headers. So...
  6. Add a dash or colon after "Current Inventory" since "Current Inventory" will appear in the header of every section. Click the Insert Sheet Name tool button, and the following code will appear where your cursor is in the Center section:
    &[Tab]
    The result, as you'll see in a moment, is that the name of the worksheet ("Inventory Status") will appear in place of this code, both on the Print Preview option, and upon printing. So according to my example, your header will look like this:
    Reporting System Current Inventory: &[Tab]

    When we view or print it, it will look like this:
    Reporting System Current Inventory: Inventory Status

    But we're not there yet. Let's move on...
  7. You want a page number on each page, except for the title page, right? So click your cursor in the Right section, and click the Insert Page Number tool button. (If you want the first page of content to start with "1" instead of "2" then add "-1" after the page number code, so it looks like this:
    &[page]-1
  8. Click OK, then click OK again. The header for every page of this particular worksheet will read as indicated above.
  9. Now... Let's take care of the column headers so they appear on every page. (By the way, in this example, the column headers will be the same in all three sections of the document.) Click the row number to highlight the row under the row containing your column headers that you want repeated on every page.
  10. Click the View menu, and select the Freeze Panes drop-down button in the Window section, and select "Freeze Panes". Now, as you scroll, you'll be able to see the column headers all the time, no matter how many rows or pages you create.
  11. Assuming this first section is laid out the way you want, it's time to create the next section of the document. Since everything will look the same in terms of formatting, you'll make a copy this sheet, then change a couple of things to get your new section. If you don't know how to create a copy this sheet, go to Step 12. If you already know how to do that, go ahead and do it, then go to Step 13.
  12. To copy this sheet, called Inventory Status, click the Home menu tab, and select the Format drop-down button in the Cells section, then select Move or Copy Sheet...
  13. Click the Create a copy checkbox, and select (move to end) so that the copy of Inventory Status appears after the Inventory Status sheet.
  14. Double-click on the new sheet title, "Inventory Status (2)" and change it to reflect the name of the second section of the document that we'll call, "Outdated Inventory".
  15. Delete the title page in this sheet since you will not want to repeat it within the document.
  16. Repeat Steps 12 - 14 for the third section of the document, and call the third section "Damaged Inventory".
And that's it. Your headers will look like this:

Section 1:
Reporting System Current Inventory: Inventory Status
Section 2:
Reporting System Current Inventory: Outdated Inventory
Section 3:
Reporting System Current Inventory: Damaged Inventory

Good luck!
Reply With Quote
  #4  
Old 05-18-2011, 03:22 PM
thebiz7 thebiz7 is offline Windows Vista Office 2007
Novice
 
Join Date: May 2011
Posts: 1
thebiz7 is on a distinguished road
Default Multiple header rows in same Excel worksheet (not different tabs)

I have multiple header rows in the same Excel worksheet (not different tabs). And I would like to format the worksheet so that when I scroll down to the second header row, the words in heading 2 replace the words in heading 1 temporarily (until I scroll back up to the top of the page). Does anyone know how to do this?
Reply With Quote
Reply

Tags
headers, multiple headers
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with headers Bill Word 0 03-10-2009 05:15 AM
Problem with headers Numeration... omniteo790 Word 0 01-26-2009 02:57 AM
TRANSFER DATA TO NEW WORKSHEET BOBN Excel 0 11-08-2007 09:34 PM
click a cell to go to a worksheet victor Excel 0 10-27-2006 02:57 PM
Include headers in table of contents bwhight Word Tables 1 10-10-2006 08:59 AM


All times are GMT -7. The time now is 08:27 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft