May 19 2021 11:04 AM
For years I have been struggling with a document that I change several times a month, and know there is an easier way to solve this issue. It seems like auto populating a header should be easy to do, right?
I have an Excel document (attached) with two Sheets, named A and B. I would like Formulas and Functions to auto populate the header on Sheet B from cells on Sheet A. There will only ever be alphanumeric used; sometimes it is a few words and other times it could be fifteen words.
On Sheet B, the 'Job Name' (seen as Room Renovation) needs to auto populate from Cell C6 on Sheet A and be centered, Bold, Ariel, 14 point font, without other text. The font will remain the same for the entire header. The next line starts with "PO #". There will not be a space followed by the 'Job Number' (seen as 4302). The header seems to be divided into thirds, so I prefer this text to be centered in the middle third header box.
This is a very simplified version of my issue. I have a complex file that has multiple Sheets with links and formulas, so the simplest solution is my preference please. Thank you to everyone who took the time to look at this for me.
May 19 2021 11:46 AM
SolutionThis requires VBA. For example in the Worksheet_Activate event:
Private Sub Worksheet_Activate()
Me.PageSetup.CenterHeader = _
"&""Arial""&14&B" & _
Worksheets("A").Range("H6").Value & vbLf & _
"PO #" & Worksheets("A").Range("C6").Value & vbLf & vbLf & _
"PRELIMINARY" & vbLf & vbLf & _
"COLOR SAMPLE REQUEST"
End Sub
May 20 2021 02:02 PM
@Hans Vogelaar I want to thank you for saving me years of frustration.
I did have to enable macros, but nothing difficult. Thank you again @Hans Vogelaar
May 20 2021 03:53 PM
May 21 2021 01:28 AM
I'm slightly confused by that, but see the attached version.
May 19 2021 11:46 AM
SolutionThis requires VBA. For example in the Worksheet_Activate event:
Private Sub Worksheet_Activate()
Me.PageSetup.CenterHeader = _
"&""Arial""&14&B" & _
Worksheets("A").Range("H6").Value & vbLf & _
"PO #" & Worksheets("A").Range("C6").Value & vbLf & vbLf & _
"PRELIMINARY" & vbLf & vbLf & _
"COLOR SAMPLE REQUEST"
End Sub