Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Footer Linking to Cells (97/2 & 2K)

    Back in the early days of Excel (pre windows) there was the ability to include data in the header or footer by reference to a cell on the worksheet. Does anyone out there know of the ability in the current Excel version?
    Thanks in advance
    Regards
    Don

  2. #2
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Footer Linking to Cells (97/2 & 2K)

    Don,
    According to the help file for Excel 2000:

    "You cannot insert graphics or cell references in a header or footer. Use print titles if you want to repeat cell contents or a graphic on every printed page. How to use print titles.

    Print row and column labels on every page

    1. Click the worksheet.
    2. On the File menu, click Page Setup, and then click the Sheet tab.
    3. To repeat column labels on every page, click Rows to repeat at top, and then enter the rows that contain the column labels.

    To repeat row labels on every page, click Columns to repeat at left, and then enter the columns that contain the row labels.

    Note Microsoft Excel prints repeating row and column labels only on the pages that include the labeled rows or columns. Pages for rows below the labeled rows or columns to the right of the labeled columns are printed without the repeating labels."

    I searched for "footer" and selected the "Create custom footers" result. (I don't think this is the result you were looking for but maybe somebody else has another idea.)

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Footer Linking to Cells (97/2 & 2K)

    Thanks Jon
    I'm really looking for a workaround to overcome the 255 character limitation in headers and footers; and am looking to the "referenced cell" approach as a potential strategy. I am certain that I was able to reference worksheet cells from within the footer in a very early version of Excel as used on a Mac with 500K of memory--circa 1989.
    Regards
    Don

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Footer Linking to Cells (97/2 & 2K)

    You can add something like the following in code:
    Worksheets("Sheet1").PageSetup.CenterFooter=range( "a1").value

    BUT, you will still be limited to the ca 255 char limit

    And it will not be live: if you change A1 you will have to rerun the macro or have the macro set to run on a change event of the sheet.

    Steve

  5. #5
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Footer Linking to Cells (97/2 & 2K)

    Holy smokes! That works!

    Question: Since that works, then why did the Help say you can't do that? That kinda makes me a little gun-shy about using the Help feature.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Footer Linking to Cells (97/2 & 2K)

    In itself, the help is correct: you can't use a cell reference in a header or footer. The code posted by Steve inserts the value of a cell, not the reference. As he pointed out, it is not "live": if you change the value of the cell, the header/footer will not change with it, you will have to run the code again to update it.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Footer Linking to Cells (97/2 & 2K)

    Of course the most useful event in this case is the Before_print event. No point in doing it for other events. The Before_print also fires when one does a print-preview.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Footer Linking to Cells (97/2 & 2K)

    My concern with the BeforePrint was that it was a workbook event and will be updating and running more than neccessary than when you are worried about a sheet event.

    In the sheetchange you can write it so you don't have to worry about the sheet name changing, in the before print event, this could be a concern.

    Steve

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Footer Linking to Cells (97/2 & 2K)

    That is true. That would be a drawback of a workbook event.
    I agree one would have to find a reliable way to determine what sheet has to receive the update to the footer and what does not.
    Maybe best is to use the codename of the sheet, I think the chance that one gets changed is very remote.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Footer Linking to Cells (97/2 & 2K)

    Concerning the 255-character limit, the workaround I can think of is to include most of the text in a text box, leaving just things like page numbers and filenames in the 'real' footer.
    It's not rocket science to copy the text box onto every page and if you set it with a transparent background and suitable wrap settings it won't obscure the real footer.
    You can also insert a graphic 'into' the text box and if you group the box and the picture you've got the equivalent of a real footer, a fake footer not constrained to 255 characters, and a picture.
    No doubt all the clever methods described above can be used to place the grouped object in the right place on specified pages.
    One drawback = excel 97 at least isn't accurately wysiwig so a bit of trial & error in print preview may be required.
    Best of luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •