Microsoft Excel - Excel formula or lookup?

Asked By Lisa Dean on 14-Jan-09 03:00 PM

How can I add a header value into a cell - is there a formula or is this a lookup? My spreadsheet has various headers and not all cells in the column have a value. I want to add the header into the cells with a value - is there a way to automate this - example one of the columns is a size column using mm, the cells have only numbers in them as the data is imported from another system and I need to add the mm to the number in the cell - is this possible to automate as I have over 3000 lines to inport and potentially about 250 different headers?

read this

C_A P replied to Lisa Dean on 15-Jan-09 05:46 AM
There is a formula called INDIRECT that can fit here, the problem is that it doesn't work with closed workbooks.  The INDIRECT formula takes a string, and if it can be interpreted as a range reference, it will return that range.  For example, if you have your date in cell A1, you could use the INDIRECT formula like:

=INDIRECT("C:\2008 Weekly Analysis\[WE " & TEXT(A1,"yyyy-mm-dd") & " WKD USA Retail Analysis.xls]USA Formulas'!$D$9")

I have also used the TEXT formula to ensure that the format of the date is the proper one.

If you need to work with closed workbooks, there is a third party add-in that has a formula called INDIRECT.EXT.  You can find it at:
http://xcell05.free.fr/morefunc/english/indirect.ext.htm
It behaves as the INDIRECT formula, adding the ability to refer to closed workbooks.

Hope this helps,

TRY THIS

C_A P replied to Lisa Dean on 15-Jan-09 05:46 AM

Cell Values In The Header

You can include the value of a cell in the header. For example, the following code will put the value of cell B5 on Sheet2 into the header of the activesheet.

ActiveSheet.PageSetup.LeftHeader = _    
    Format(Worksheets("Sheet2").Range("B5").Value)

Full File Name In The Header

The following code will place the full file name, including the path, into the header of the active worksheet.

ActiveSheet.PageSetup.LeftHeader = ThisWorkbook.FullName

Fonts And Font Sizes

You can insert control codes to customize the font, font attributes (bold, italics, etc), and font size. The control code used in the header string is the ampersand &.   The code below will create put the follow formatted text in the header.

Some Bold Courier New Size 10 Text

Worksheets("Sheet2").PageSetup.LeftHeader = _
"Some&B&""Courier New""&10 Bold Courier New Size 10&B&""Arial"" Text"

Note that since the font name must be enclosed in quotes in the header string itself, you must use two double quotes in VBA.

See Formatting Codes For Headers And Footers in the VBA help files for a complete list of control sequences.

Where To Put VBA Code

If you are using Excel97, you can include the VBA code to set up your page headers and footers in the Workbook's BeforePrint event procedure.  For more information about event procedures, click here.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim WS As Worksheet
For Each WS in Worksheets
    WS.PageSetup.LeftHeader = ThisWorkbook.FullName & " " & _
        Worksheets("Sheet2").Range("A5").Value
Next WS
End Sub

This will update the headers each time a worksheet in the workbook is printed.

TRY THIS

C_A P replied to Lisa Dean on 15-Jan-09 05:47 AM
			
Option Explicit 
 
Sub AddHeader_CurrentSheetOnly() 
     'Add A1 from active sheet to active sheet's header
    With ActiveSheet.PageSetup 
        .LeftHeader = Range("A1").Value 
    End With 
End Sub 
 
Sub AddHeaderToAll_FromEachSheet() 
     'Add A1 from each sheet to that sheet's header
    Dim ws As Worksheet 
    For Each ws In ActiveWorkbook.Worksheets 
        ws.PageSetup.LeftHeader = ws.Range("A1").Value 
    Next ws 
End Sub 
 
Sub AddHeaderToAll_FromCurrentSheet() 
     'Add A1 from active sheet to each sheets's header
    Dim ws As Worksheet 
    For Each ws In ActiveWorkbook.Worksheets 
        ws.PageSetup.LeftHeader = ActiveSheet.Range("A1").Value 
    Next ws 
End Sub

How to use:

  1. Copy the desired procedure from the above code (one set from Sub to End Sub).
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. Enter some data in cell A1 (on each sheet if necessary).
  2. Press Alt + F8 to display the Macro dialog box.
  3. Choose the name of the macro you copied and click Run.
  4. Go to File|Page Setup|Header/Footer and verify that your header has been set correctly.
 
TRY THIS LINK
C_A P replied to Lisa Dean on 15-Jan-09 05:47 AM
http://support.microsoft.com/kb/273028