I have created a BOM template. It is used by another program to create an
Excel spreadsheet BOM. The first column is an "Item" number column. There is
a formula in that column to automatically number every row that has an item
in it: =IF(B4="","",ROW()-2). I formatted 1000 rows so any BOM can fit. My
problem is that if you don't " set the Print Area before you print, you get
the 1000 lines printed. I would like to use my Item number technique to also
set the print area automatically when the BOM is created using the template.
Does anyone know how this might be accomplished? Thansk for the help.
--
Jim

JCMII wrote:
I have created a BOM template. It is used by another program to create an
Excel spreadsheet BOM. The first column is an "Item" number column. There is
a formula in that column to automatically number every row that has an item
in it: =IF(B4="","",ROW()-2). I formatted 1000 rows so any BOM can fit. My
problem is that if you don't " set the Print Area before you print, you get
the 1000 lines printed. I would like to use my Item number technique to also
set the print area automatically when the BOM is created using the template.
Does anyone know how this might be accomplished? Thansk for the help.


Try this for Print_Area definition in the Define Name dialog:


=INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))


Adjust the ":F" to reference the rightmost used column in your sheet.

You can create a dynamic range name called 'Printarea'. Choose Insert Name
Define and in the 'Refers to' at the bottom you will put:

=OFFSET($a$4,0,0,counta($b$4:$b$1000),5)

This example will set the range name from the first cell in column a to the
last non-blank row based on column b, and 5 columns out to the right.

When you select page setup, you will put the range 'Printarea' in the Print
Area box on the 'Sheet' tab.

"JCMII" wrote:

I have created a BOM template. It is used by another program to create an
Excel spreadsheet BOM. The first column is an "Item" number column. There is
a formula in that column to automatically number every row that has an item
in it: =IF(B4="","",ROW()-2). I formatted 1000 rows so any BOM can fit. My
problem is that if you don't " set the Print Area before you print, you get
the 1000 lines printed. I would like to use my Item number technique to also
set the print area automatically when the BOM is created using the template.
Does anyone know how this might be accomplished? Thansk for the help.
--
Jim

Glenn wrote:
JCMII wrote:
I have created a BOM template. It is used by another program to create
an Excel spreadsheet BOM. The first column is an "Item" number column.
There is a formula in that column to automatically number every row
that has an item in it: =IF(B4="","",ROW()-2). I formatted 1000 rows
so any BOM can fit. My problem is that if you don't " set the Print
Area before you print, you get the 1000 lines printed. I would like to
use my Item number technique to also set the print area automatically
when the BOM is created using the template. Does anyone know how this
might be accomplished? Thansk for the help.


Try this for Print_Area definition in the Define Name dialog:


=INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))


Adjust the ":F" to reference the rightmost used column in your sheet.


Or, this might be better:

=INDIRECT("A1:F"&MAX(IF(Sheet1!$A$1:$A$1000<"",RO W(Sheet1!$A$1:$A$1000),"")))

Saved from a previous post (so you'll have to adjust the sheet names and column
letters and even the columns to print (A:X or B:z????):

If those formulas appear at the end of the data and you don't want to use
autofilter...

If I can pick out a column indicates if that row is used or not, then I like
this technique:

(I used column A in my sample, but you can use any column you want.)

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) )

(Make that 1000 big enough to extend past the last possible row.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)

That last 3 represents the last column to print (A:C in my example).

And change the worksheet (sheet1) if necessary (in all the places).

If you go into file|page setup, you may find that the print range is changed to
a specific range. And you'll have to reapply the Print_Area name.

JCMII wrote:

I have created a BOM template. It is used by another program to create an
Excel spreadsheet BOM. The first column is an "Item" number column. There is
a formula in that column to automatically number every row that has an item
in it: =IF(B4="","",ROW()-2). I formatted 1000 rows so any BOM can fit. My
problem is that if you don't " set the Print Area before you print, you get
the 1000 lines printed. I would like to use my Item number technique to also
set the print area automatically when the BOM is created using the template.
Does anyone know how this might be accomplished? Thansk for the help.
--
Jim

--

Dave Peterson

Your suggestion seems good, but I seem to have one problem. The COUNTA
function is causing Excel to give an error: "A function in this formula
causes the result to change each time the spreadsheet is calculated..."
Everything I have looked at looks good to me so I don't know why I get this
error. I will continue to try to get it to work. It seems like a good
solution. By the way, I'm using Excel 2007 and saving as Excel 2003 .xlt. I
don't think that should matter, except, I noticed that there seems to be
quite a bit more error checking in 2007. I am going to look to see if the
problem is too much error checking.

--
Jim


"cm" wrote:

You can create a dynamic range name called 'Printarea'. Choose Insert Name
Define and in the 'Refers to' at the bottom you will put:

=OFFSET($a$4,0,0,counta($b$4:$b$1000),5)

This example will set the range name from the first cell in column a to the
last non-blank row based on column b, and 5 columns out to the right.

When you select page setup, you will put the range 'Printarea' in the Print
Area box on the 'Sheet' tab.

"JCMII" wrote:

I have created a BOM template. It is used by another program to create an
Excel spreadsheet BOM. The first column is an "Item" number column. There is
a formula in that column to automatically number every row that has an item
in it: =IF(B4="","",ROW()-2). I formatted 1000 rows so any BOM can fit. My
problem is that if you don't " set the Print Area before you print, you get
the 1000 lines printed. I would like to use my Item number technique to also
set the print area automatically when the BOM is created using the template.
Does anyone know how this might be accomplished? Thansk for the help.
--
Jim

Here is a rev Controlled BOM

http://www.run8tech.com/tools.aspx

On Monday, September 28, 2009 11:24 AM JCMII wrote:

I have created a BOM template. It is used by another program to create an
Excel spreadsheet BOM. The first column is an "Item" number column. There is
a formula in that column to automatically number every row that has an item
in it: =IF(B4="","",ROW()-2). I formatted 1000 rows so any BOM can fit. My
problem is that if you do not " set the Print Area before you print, you get
the 1000 lines printed. I would like to use my Item number technique to also
set the print area automatically when the BOM is created using the template.
Does anyone know how this might be accomplished? Thansk for the help.
--
Jim


On Monday, September 28, 2009 11:40 AM Glenn wrote:

JCMII wrote:


Try this for Print_Area definition in the Define Name dialog:


=INDIRECT("A1:F"&COUNTIF(Sheet1!A:A,"0"))


Adjust the ":F" to reference the rightmost used column in your sheet.


On Monday, September 28, 2009 11:42 AM cm wrote:

You can create a dynamic range name called 'Printarea'. Choose Insert Name
Define and in the 'Refers to' at the bottom you will put:

=OFFSET($a$4,0,0,counta($b$4:$b$1000),5)

This example will set the range name from the first cell in column a to the
last non-blank row based on column b, and 5 columns out to the right.

When you select page setup, you will put the range 'Printarea' in the Print
Area box on the 'Sheet' tab.

"JCMII" wrote:


On Monday, September 28, 2009 12:20 PM Glenn wrote:

Glenn wrote:


Or, this might be better:

=INDIRECT("A1:F"&MAX(IF(Sheet1!$A$1:$A$1000<"",RO W(Sheet1!$A$1:$A$1000),"")))


On Monday, September 28, 2009 2:01 PM Dave Peterson wrote:

Saved from a previous post (so you will have to adjust the sheet names and column
letters and even the columns to print (A:X or B:z????):

If those formulas appear at the end of the data and you do not want to use
autofilter...

If I can pick out a column indicates if that row is used or not, then I like
this technique:

(I used column A in my sample, but you can use any column you want.)

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) )

(Make that 1000 big enough to extend past the last possible row.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)

That last 3 represents the last column to print (A:C in my example).

And change the worksheet (sheet1) if necessary (in all the places).

If you go into file|page setup, you may find that the print range is changed to
a specific range. And you will have to reapply the Print_Area name.

JCMII wrote:

--

Dave Peterson


On Monday, September 28, 2009 3:09 PM JCMII wrote:

Your suggestion seems good, but I seem to have one problem. The COUNTA
function is causing Excel to give an error: "A function in this formula
causes the result to change each time the spreadsheet is calculated..."
Everything I have looked at looks good to me so I do not know why I get this
error. I will continue to try to get it to work. It seems like a good
solution. By the way, I am using Excel 2007 and saving as Excel 2003 .xlt. I
do not think that should matter, except, I noticed that there iseems to be
quite a bit more error checking in 2007. I am going to look to see if the
problem is too much error checking.

--
Jim


"cm" wrote: