This might be tricky

[Al Chara]

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

 

[Aladin Akyurek]

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

 

[Al Chara]

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?

 

[Aladin Akyurek]

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

 

[NateO]

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