Dynamically change header based on worksheet

[wrmcmahan12]

Is it possible to dynamically set a print header depending on the page that's being printed?

Basically I need to create an attendance tracker that's used by the people inputting the data, and the people tracking it. Each worksheet is assigned to a specific supervisor. The supervisors will print the page, write in the attendance codes, and a clerk will enter the codes in an Excel sheet. The issue is the supervisors view and the clerk's input view require different info as follows:

Supervisors requirements: Current date, legend of input codes, supervisors name

Clerk: nothing.

I'd ideally like to setup a way so that when a supervisor prints the page, their requirements are printed at the top. I know how to include the dynamic date, but to define the supervisors name isn't clear to me. I also would like to avoid putting this info in the worksheet itself because the way the "freeze panes" is set, it's too much extra info at the top for the clerk to have a good size view to work in (unless hiding rows, but I'd hope there's a better way).

So is it possible to set a custom header that passes the worksheets name to the header (which is the supervisors name)?

 

[Oaktree]

Why not put the desired header at the top of the sheet but freeze the panes so that those header rows are off the page (i.e. above the first visible row after freezing panes)? That way, you won't see them on the screen, but they'll still print.

Otherwise, you can use VBA to accomplish this through a workbook_beforeprint event. See Chip's notes here:

http://www.cpearson.com/excel/headfoot.htm