Print Area
 

I have a sheet that has various formulas that return blank cells. How
do I set the print area dynamically so that is stops based on a
column's length?

Any Ideas? The sheet length changes daily and I don't want user to
have to change settings at all...

Print Area
 

I like this approach (with those formulas in column A):


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).

Nimish wrote:

I have a sheet that has various formulas that return blank cells. How
do I set the print area dynamically so that is stops based on a
column's length?

Any Ideas? The sheet length changes daily and I don't want user to
have to change settings at all...

--

Dave Peterson

Print Area
 

Dave,

Does this technique work if I need to replicate this for 14 different
worksheets in the same workbook? I thin I am getting some name
conflicts that I don't understand how to deal with.

Nimish


Dave Peterson wrote:
I like this approach (with those formulas in column A):


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).

Nimish wrote:

I have a sheet that has various formulas that return blank cells. How
do I set the print area dynamically so that is stops based on a
column's length?

Any Ideas? The sheet length changes daily and I don't want user to
have to change settings at all...

--

Dave Peterson

Print Area
 

The parts that you have to be careful with:

Names in workbook: Sheet1!LastRow
Names in workbook: Sheet1!Print_Area

Make sure you include the sheet name when you do this.

But some sheet names require apostrophes surrounding the names (just like a
formula in a cell):

Names in workbook: 'Sheet1'!LastRow
Names in workbook: 'Sheet1'!Print_Area

If you always use the apostrophes, excel won't mind. If you need them and don't
use them, you'll have trouble.

And change Sheet1 to whatever worksheet you're working on.

Names in workbook: 'My SheetName Here'!LastRow
Names in workbook: 'My SheetName Here'!Print_Area



Nimish wrote:

Dave,

Does this technique work if I need to replicate this for 14 different
worksheets in the same workbook? I thin I am getting some name
conflicts that I don't understand how to deal with.

Nimish

Dave Peterson wrote:
I like this approach (with those formulas in column A):


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).

Nimish wrote:

I have a sheet that has various formulas that return blank cells. How
do I set the print area dynamically so that is stops based on a
column's length?

Any Ideas? The sheet length changes daily and I don't want user to
have to change settings at all...

--

Dave Peterson

--

Dave Peterson

Print Area
 

Dave,

Never got back to you, but thank you very much.


Dave Peterson wrote:
The parts that you have to be careful with:

Names in workbook: Sheet1!LastRow
Names in workbook: Sheet1!Print_Area

Make sure you include the sheet name when you do this.

But some sheet names require apostrophes surrounding the names (just like a
formula in a cell):

Names in workbook: 'Sheet1'!LastRow
Names in workbook: 'Sheet1'!Print_Area

If you always use the apostrophes, excel won't mind. If you need them and don't
use them, you'll have trouble.

And change Sheet1 to whatever worksheet you're working on.

Names in workbook: 'My SheetName Here'!LastRow
Names in workbook: 'My SheetName Here'!Print_Area



Nimish wrote:

Dave,

Does this technique work if I need to replicate this for 14 different
worksheets in the same workbook? I thin I am getting some name
conflicts that I don't understand how to deal with.

Nimish

Dave Peterson wrote:
I like this approach (with those formulas in column A):


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).

Nimish wrote:

I have a sheet that has various formulas that return blank cells. How
do I set the print area dynamically so that is stops based on a
column's length?

Any Ideas? The sheet length changes daily and I don't want user to
have to change settings at all...

--

Dave Peterson

--

Dave Peterson

Print Area
 

Glad it worked for you.

Nimish wrote:

Dave,

Never got back to you, but thank you very much.

Dave Peterson wrote:
The parts that you have to be careful with:

Names in workbook: Sheet1!LastRow
Names in workbook: Sheet1!Print_Area

Make sure you include the sheet name when you do this.

But some sheet names require apostrophes surrounding the names (just like a
formula in a cell):

Names in workbook: 'Sheet1'!LastRow
Names in workbook: 'Sheet1'!Print_Area

If you always use the apostrophes, excel won't mind. If you need them and don't
use them, you'll have trouble.

And change Sheet1 to whatever worksheet you're working on.

Names in workbook: 'My SheetName Here'!LastRow
Names in workbook: 'My SheetName Here'!Print_Area



Nimish wrote:

Dave,

Does this technique work if I need to replicate this for 14 different
worksheets in the same workbook? I thin I am getting some name
conflicts that I don't understand how to deal with.

Nimish

Dave Peterson wrote:
I like this approach (with those formulas in column A):


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).

Nimish wrote:

I have a sheet that has various formulas that return blank cells. How
do I set the print area dynamically so that is stops based on a
column's length?

Any Ideas? The sheet length changes daily and I don't want user to
have to change settings at all...

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson