Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: This might be tricky

  1. #1
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all,

    I have a sheet with two columns. Column A has part numbers organized by department and Column B has the cost per part number. I have a macro that sets the print area depending on the department.

    I would like to have a way to print the total cost of all the part numbers in a department. The parts list is automatically generated so I cannot add any cells inside the list that will sum the cost per department. But I want a way to display the total cost for a department when I print each department's parts list. I was thinking that I have two options:

    1. put a formula in the header (if this is possible)
    2. put a formula at the top of the document in the first row and have that be the Print Title (so it prints on each page).

    I would need the formula be able to generate the proper SUM for each department. Basically I need to enter a formula in a cell or header that will sum a range of cells. The problem that I run into is that my range is stored as a variable and I cannot use the variable in a formula in the worksheet.

    Can anyone give me any help or suggestions on solving my problem.

    Kind regards,
    Al

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-25 11:51, Al Chara wrote:
    Hi all,

    I have a sheet with two columns. Column A has part numbers organized by department and Column B has the cost per part number. I have a macro that sets the print area depending on the department.

    I would like to have a way to print the total cost of all the part numbers in a department. The parts list is automatically generated so I cannot add any cells inside the list that will sum the cost per department. But I want a way to display the total cost for a department when I print each department's parts list. I was thinking that I have two options:

    1. put a formula in the header (if this is possible)
    2. put a formula at the top of the document in the first row and have that be the Print Title (so it prints on each page).

    I would need the formula be able to generate the proper SUM for each department. Basically I need to enter a formula in a cell or header that will sum a range of cells. The problem that I run into is that my range is stored as a variable and I cannot use the variable in a formula in the worksheet.

    Can anyone give me any help or suggestions on solving my problem.

    Kind regards,
    Al
    Would

    INDIRECT(variable-that-stands-for-range) as part in your totaling formula not work?

    Aladin

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you Aladin. It worked great. I put the formula in my PrintTitle Area. Is it possible to add formulas into the header and footer?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-25 12:56, Al Chara wrote:
    Thank you Aladin. It worked great. I put the formula in my PrintTitle Area. Is it possible to add formulas into the header and footer?
    Not ordinarly as far as I know. The WBA army might want to take that up. I think it would not be unreasonable to start a new thread with that specific question to attract the attention of that army!

    Aladin

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm already attracted...The following worked for me:

    Sub head_set()
    With ActiveSheet.PageSetup
    .CenterHeader = WorksheetFunction.Sum(Range("a1:a10"))
    End With
    End Sub

    Might want to adjust the position (e.g., .rightfooter, .leftheader, etc..) and formula.

    Cheers, Nate

    [ This Message was edited by: NateO on 2002-02-25 13:42 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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