Can contents of a cell be used in header/footer?

All,

I am interested in making a custom header that contains text from a certain
cell.  There are standard fields that can be added to a header or footer
using the standard buttons in the header or footer dialog box.

These are:
&[Page], &[Pages], &[Date], &[Time], &[Path]&[File], &[File], &[Tab]

I was wondering if I could create a custom field for my header that would
insert the contents of a certain cell.

For instance, cell A7 contains a Vendor name, let's say "XYZ Supply".  I
would like to create a header that says, "Vendor: XYZ Supply" by creating a
custom header something like: Vendor: &[Cell A7].  I've tried a number of
configurations for this custom field, but haven't found a way to do it yet.

Does anyone know how to do it?

Thanks,

Scott
webbweenie@hotmail.com



0
anon8110 (5)
6/14/2004 5:06:13 PM
excel 39879 articles. 2 followers. Follow

6 Replies
679 Views

Similar Articles

[PageSpeed] 33

Hi
not possible without using VBA. Put the following code in your workbook
module (not in a standard
module):
Private Sub Workbook BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
   For Each wkSht In me.Worksheets
     With wkSht.PageSetup
        .CenterFooter = wks.range("A1").value
      End With
   Next wkSht
End Sub

This will insert cell A1 of each sheet in the footer

--
Regards
Frank Kabel
Frankfurt, Germany


Anon Ymous wrote:
> All,
>
> I am interested in making a custom header that contains text from a
> certain cell.  There are standard fields that can be added to a
> header or footer using the standard buttons in the header or footer
> dialog box.
>
> These are:
> &[Page], &[Pages], &[Date], &[Time], &[Path]&[File], &[File], &[Tab]
>
> I was wondering if I could create a custom field for my header that
> would insert the contents of a certain cell.
>
> For instance, cell A7 contains a Vendor name, let's say "XYZ Supply".
> I would like to create a header that says, "Vendor: XYZ Supply" by
> creating a custom header something like: Vendor: &[Cell A7].  I've
> tried a number of configurations for this custom field, but haven't
> found a way to do it yet.
>
> Does anyone know how to do it?
>
> Thanks,
>
> Scott
> webbweenie@hotmail.com

0
frank.kabel (11126)
6/14/2004 5:16:44 PM
One way:

Put this in the ThisWorbook code module (right-click on the workbook 
title bar and choose View Code:


    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        Dim wsSheet As Worksheet
        For Each wsSheet In ActiveWindow.SelectedSheets
            With wsSheet
                If .Name = "Sheet1" Then _
                    .PageSetup.LeftHeader = "Vendor: " & _
                         .Range("A7").Text
        Next wsSheet
    End Sub

Change "Sheet1" to the name of your sheet.

In article <e1ltoHjUEHA.3420@TK2MSFTNGP12.phx.gbl>,
 "Anon Ymous" <anon@ymous.com> wrote:

> All,
> 
> I am interested in making a custom header that contains text from a certain
> cell.  There are standard fields that can be added to a header or footer
> using the standard buttons in the header or footer dialog box.
> 
> These are:
> &[Page], &[Pages], &[Date], &[Time], &[Path]&[File], &[File], &[Tab]
> 
> I was wondering if I could create a custom field for my header that would
> insert the contents of a certain cell.
> 
> For instance, cell A7 contains a Vendor name, let's say "XYZ Supply".  I
> would like to create a header that says, "Vendor: XYZ Supply" by creating a
> custom header something like: Vendor: &[Cell A7].  I've tried a number of
> configurations for this custom field, but haven't found a way to do it yet.
> 
> Does anyone know how to do it?
> 
> Thanks,
> 
> Scott
> webbweenie@hotmail.com
0
jemcgimpsey (6723)
6/14/2004 5:21:16 PM
Greetings,

This code works if you put an "End With" line after the ".Range" line.
I was able to put the cell contents in the RightHeader and place my leading
text (PO for vender:) in the Center Header.
I was hoping to define the cell as a variable that I could dynamically
include in my header text so it could be all concatinated together in one
location, but this will do for now.

Thanks!!

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-B659E0.11211614062004@msnews.microsoft.com...
> One way:
>
> Put this in the ThisWorbook code module (right-click on the workbook
> title bar and choose View Code:
>
>
>     Private Sub Workbook_BeforePrint(Cancel As Boolean)
>         Dim wsSheet As Worksheet
>         For Each wsSheet In ActiveWindow.SelectedSheets
>             With wsSheet
>                 If .Name = "Sheet1" Then _
>                     .PageSetup.LeftHeader = "Vendor: " & _
>                          .Range("A7").Text
>         Next wsSheet
>     End Sub
>
> Change "Sheet1" to the name of your sheet.
>
> In article <e1ltoHjUEHA.3420@TK2MSFTNGP12.phx.gbl>,
>  "Anon Ymous" <anon@ymous.com> wrote:
>
> > All,
> >
> > I am interested in making a custom header that contains text from a
certain
> > cell.  There are standard fields that can be added to a header or footer
> > using the standard buttons in the header or footer dialog box.
> >
> > These are:
> > &[Page], &[Pages], &[Date], &[Time], &[Path]&[File], &[File], &[Tab]
> >
> > I was wondering if I could create a custom field for my header that
would
> > insert the contents of a certain cell.
> >
> > For instance, cell A7 contains a Vendor name, let's say "XYZ Supply".  I
> > would like to create a header that says, "Vendor: XYZ Supply" by
creating a
> > custom header something like: Vendor: &[Cell A7].  I've tried a number
of
> > configurations for this custom field, but haven't found a way to do it
yet.
> >
> > Does anyone know how to do it?
> >
> > Thanks,
> >
> > Scott
> > webbweenie@hotmail.com


0
anon8110 (5)
6/14/2004 5:55:40 PM
Not as sophisticated as these other guys, so I use ASAP Utilities t
change sheet tab to cell content eg A1, then simply use header setting
to select sheet tab in left, centre or right. The cell contents of A
can of course be concatenated data from any number of cells in eac
sheet

--
Message posted from http://www.ExcelForum.com

0
6/14/2004 10:40:39 PM
Maybe you could use a range name on that worksheet and point at a different cell
when you want a different location:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        Dim wsSheet As Worksheet
        For Each wsSheet In ActiveWindow.SelectedSheets
            With wsSheet
                If .Name = "Sheet1" Then _
                    .PageSetup.LeftHeader = "Vendor: " & _
                         .Range("VendorName").Text
            End With
        Next wsSheet
    End Sub

Use Insert|Name|Define to create the name and to change to a different location.

Anon Ymous wrote:
> 
> All,
> 
> I am interested in making a custom header that contains text from a certain
> cell.  There are standard fields that can be added to a header or footer
> using the standard buttons in the header or footer dialog box.
> 
> These are:
> &[Page], &[Pages], &[Date], &[Time], &[Path]&[File], &[File], &[Tab]
> 
> I was wondering if I could create a custom field for my header that would
> insert the contents of a certain cell.
> 
> For instance, cell A7 contains a Vendor name, let's say "XYZ Supply".  I
> would like to create a header that says, "Vendor: XYZ Supply" by creating a
> custom header something like: Vendor: &[Cell A7].  I've tried a number of
> configurations for this custom field, but haven't found a way to do it yet.
> 
> Does anyone know how to do it?
> 
> Thanks,
> 
> Scott
> webbweenie@hotmail.com

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/14/2004 11:09:00 PM
Typo alert:

..CenterFooter = wks.range("A1").value
should be:
..CenterFooter = wksht.range("A1").value



Frank Kabel wrote:
> 
> Hi
> not possible without using VBA. Put the following code in your workbook
> module (not in a standard
> module):
> Private Sub Workbook BeforePrint(Cancel As Boolean)
> Dim wkSht As Worksheet
>    For Each wkSht In me.Worksheets
>      With wkSht.PageSetup
>         .CenterFooter = wks.range("A1").value
>       End With
>    Next wkSht
> End Sub
> 
> This will insert cell A1 of each sheet in the footer
> 
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
> 
> Anon Ymous wrote:
> > All,
> >
> > I am interested in making a custom header that contains text from a
> > certain cell.  There are standard fields that can be added to a
> > header or footer using the standard buttons in the header or footer
> > dialog box.
> >
> > These are:
> > &[Page], &[Pages], &[Date], &[Time], &[Path]&[File], &[File], &[Tab]
> >
> > I was wondering if I could create a custom field for my header that
> > would insert the contents of a certain cell.
> >
> > For instance, cell A7 contains a Vendor name, let's say "XYZ Supply".
> > I would like to create a header that says, "Vendor: XYZ Supply" by
> > creating a custom header something like: Vendor: &[Cell A7].  I've
> > tried a number of configurations for this custom field, but haven't
> > found a way to do it yet.
> >
> > Does anyone know how to do it?
> >
> > Thanks,
> >
> > Scott
> > webbweenie@hotmail.com

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/14/2004 11:09:43 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Letter Writing assitant to be used with Prospects
Would like to see Letter Writing Assitant to be extended to be used with Prospects. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=73b8a531-e5c3-4d8b-91ae-5b1758b9f1a5&...

Can't insert Char(1) into SQL Server from Stored Procedure
Hello I am trying to insert a char(1) field into a table from an ASP.Net/C# application. At first I was inserting rows into the table but none of the columns that I was inserting the rows into were char(1) columns, they were mostly int and varchar columns. Then I found out that there was one column in the list that was not supposed to be an int column but instead it was supposed to be one of the char(1) columns into the table. So I replaced the int column in the list with the char(1) column and since the value of the char(1) column resulted from a CheckBox ('Y...

Can E-mail recipient policy be linked to the OU membership?
Running Exchange 2003 (mixed mode) and Windows 2003 server (Windows 2000 mixed mode): I'd like to create a e-mail recipient policy that is linked to the different OU's that are created on the system. Is it possible to link the policy to a specific OU or do I need to create groups on the AD to control which recipients a policy applies to? Martin Moustgaard Yes, you can create a recipient policy that looks to just a specific OU. It is done through LDAP queries. Here are some references to articles and whitepapers that discuss how to work with recipient policies. 249299.KB.EN-US HO...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

can i download lxce serv.exe
want to open message lxce serv.exe using windows xp ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

how many receivers i can add
i want to use outlook send 2000 thousands emails to 2000 peoples at the same time. can i put them all in the recivers and send them at one time? thank you for you help ...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

With and import tool can you change only item description?
Is there a way to change only the item description on a large quanity of items. What about the extended description? Thanks for your help. Use the MS SQL Data Import Tool by EMS. $65.00. The QSImport Tool available to download from Microsoft will probably work but is not supported by Microsoft. Kinnard L. Kohler Business Machines Systems 6101 South Shackleford Road Little Rock, AR 72204-8606 (T) 501-375-8380 (F) 501-375-0043 (Cell) 501-412-5686 Email: kinnard@removebmsar.com "Lisa" wrote: > Is there a way to change only the item description on a large quanity of >...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Can't configure Outlook Express
I had Outlook express set up for the Money newsgroup, but somehow I lost it. Now I even forgot how to set it up again. I always get the error message saying "server cannot be found". The server name I typed in was microsoft.public.money, and I am not sure what should I type in here Thanks for help In microsoft.public.money, wj wrote: >I had Outlook express set up for the Money newsgroup, but >somehow I lost it. Now I even forgot how to set it up >again. I always get the error message saying "server >cannot be found". The server name I typed in was &g...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

what printers can run Vista
just purchased a dell which came with Vista on it. now my printer (HP 5550 deskjet) won't print. I need to find a new printer that i can hook up to the computer. This is an Access newsgroup. We help with questions related to Microsoft Access - the database application that is part of MS Office Professional. -- Rick B "monroe" <monroe@discussions.microsoft.com> wrote in message news:AC746826-251A-4097-AF88-2805BCDE7DEC@microsoft.com... > just purchased a dell which came with Vista on it. now my printer (HP > 5550 > deskjet) won't print. I need to...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...