I have added an extra column to a pivot table to analyze the results. The
pivot table is set up with a page filter for separate users. Each user can
select their data to review without having to look at the entire data set
which is 240 pages. I have used if statements so that the formula rin the
added column runs only if there is data. However if the user selects page
preview they still think their report is 240 pages long because of the
formulas in the added column. Is there any way to avoid this?

You can define a dynamic print area for the worksheet:

Select the pivot table worksheet
Choose InsertNameDefine
In the Names in Workbook box, type: 'SalesPivot'!Print_Area
using your sheet name instead of SalesPivot
In the Refers to box, type a formula based on a column that has a value
in every row, e.g. the Grand Total column:

=OFFSET('SalesPivot'!$A$1,0,0,COUNTA('SalesPivot'! $H:$H)
-COUNTA('SalesPivot'!$H$1:$H$10)+10,11)

In this example, the values start in cell H11, and the print area
will be 11 columns wide.

Click the Add button
Click OK

Ekazakoff wrote:
I have added an extra column to a pivot table to analyze the results. The
pivot table is set up with a page filter for separate users. Each user can
select their data to review without having to look at the entire data set
which is 240 pages. I have used if statements so that the formula rin the
added column runs only if there is data. However if the user selects page
preview they still think their report is 240 pages long because of the
formulas in the added column. Is there any way to avoid this?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks Debra but I couldn't make this work. I didn't have a grand total on
my data so I did add that thinking that per your suggestion the grand total
row would be the final row of the print area. However I don't understand the
offset formula you have written. You use h10 in your formula dn then you add
10. Does this imply that you need to know the row length of the report? I
will have at least 8 users each having a different report length. Any
clarification would be appreciated.

"Debra Dalgleish" wrote:

You can define a dynamic print area for the worksheet:

Select the pivot table worksheet
Choose InsertNameDefine
In the Names in Workbook box, type: 'SalesPivot'!Print_Area
using your sheet name instead of SalesPivot
In the Refers to box, type a formula based on a column that has a value
in every row, e.g. the Grand Total column:

=OFFSET('SalesPivot'!$A$1,0,0,COUNTA('SalesPivot'! $H:$H)
-COUNTA('SalesPivot'!$H$1:$H$10)+10,11)

In this example, the values start in cell H11, and the print area
will be 11 columns wide.

Click the Add button
Click OK

Ekazakoff wrote:
I have added an extra column to a pivot table to analyze the results. The
pivot table is set up with a page filter for separate users. Each user can
select their data to review without having to look at the entire data set
which is 240 pages. I have used if statements so that the formula rin the
added column runs only if there is data. However if the user selects page
preview they still think their report is 240 pages long because of the
formulas in the added column. Is there any way to avoid this?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

In the formula:

=OFFSET('SalesPivot'!$A$1,0,0,COUNTA('SalesPivot'! $H:$H)
-COUNTA('SalesPivot'!$H$1:$H$10)+10,11)

SalesPivot is the name of the worksheet, and cell A1 on that sheet is
used as a reference point.
From there, the offset is zero rows and zero columns as a new starting
point.
The number of row is determined by counting the items in column H, so it
is assumed that there is an entry in every row of the pivot table, in
that column.
There may be headings in H1:H10, so those are counted, and subtracted
from the count of items.
Then, 10 is added to the count, because there are 10 rows in the heading
area.
The result of the counts should equal the last row in the pivot table.

Ekazakoff wrote:
Thanks Debra but I couldn't make this work. I didn't have a grand total on
my data so I did add that thinking that per your suggestion the grand total
row would be the final row of the print area. However I don't understand the
offset formula you have written. You use h10 in your formula dn then you add
10. Does this imply that you need to know the row length of the report? I
will have at least 8 users each having a different report length. Any
clarification would be appreciated.

"Debra Dalgleish" wrote:


You can define a dynamic print area for the worksheet:

Select the pivot table worksheet
Choose InsertNameDefine
In the Names in Workbook box, type: 'SalesPivot'!Print_Area
using your sheet name instead of SalesPivot
In the Refers to box, type a formula based on a column that has a value
in every row, e.g. the Grand Total column:

=OFFSET('SalesPivot'!$A$1,0,0,COUNTA('SalesPivot'! $H:$H)
-COUNTA('SalesPivot'!$H$1:$H$10)+10,11)

In this example, the values start in cell H11, and the print area
will be 11 columns wide.

Click the Add button
Click OK

Ekazakoff wrote:

I have added an extra column to a pivot table to analyze the results. The
pivot table is set up with a page filter for separate users. Each user can
select their data to review without having to look at the entire data set
which is 240 pages. I have used if statements so that the formula rin the
added column runs only if there is data. However if the user selects page
preview they still think their report is 240 pages long because of the
formulas in the added column. Is there any way to avoid this?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks so much for the additional clarification Debra. It worked like a charm!

"Debra Dalgleish" wrote:

In the formula:

=OFFSET('SalesPivot'!$A$1,0,0,COUNTA('SalesPivot'! $H:$H)
-COUNTA('SalesPivot'!$H$1:$H$10)+10,11)

SalesPivot is the name of the worksheet, and cell A1 on that sheet is
used as a reference point.
From there, the offset is zero rows and zero columns as a new starting
point.
The number of row is determined by counting the items in column H, so it
is assumed that there is an entry in every row of the pivot table, in
that column.
There may be headings in H1:H10, so those are counted, and subtracted
from the count of items.
Then, 10 is added to the count, because there are 10 rows in the heading
area.
The result of the counts should equal the last row in the pivot table.

Ekazakoff wrote:
Thanks Debra but I couldn't make this work. I didn't have a grand total on
my data so I did add that thinking that per your suggestion the grand total
row would be the final row of the print area. However I don't understand the
offset formula you have written. You use h10 in your formula dn then you add
10. Does this imply that you need to know the row length of the report? I
will have at least 8 users each having a different report length. Any
clarification would be appreciated.

"Debra Dalgleish" wrote:


You can define a dynamic print area for the worksheet:

Select the pivot table worksheet
Choose InsertNameDefine
In the Names in Workbook box, type: 'SalesPivot'!Print_Area
using your sheet name instead of SalesPivot
In the Refers to box, type a formula based on a column that has a value
in every row, e.g. the Grand Total column:

=OFFSET('SalesPivot'!$A$1,0,0,COUNTA('SalesPivot'! $H:$H)
-COUNTA('SalesPivot'!$H$1:$H$10)+10,11)

In this example, the values start in cell H11, and the print area
will be 11 columns wide.

Click the Add button
Click OK

Ekazakoff wrote:

I have added an extra column to a pivot table to analyze the results. The
pivot table is set up with a page filter for separate users. Each user can
select their data to review without having to look at the entire data set
which is 240 pages. I have used if statements so that the formula rin the
added column runs only if there is data. However if the user selects page
preview they still think their report is 240 pages long because of the
formulas in the added column. Is there any way to avoid this?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

You're welcome! Thanks for letting me know that it worked.

Ekazakoff wrote:
Thanks so much for the additional clarification Debra. It worked like a charm!

"Debra Dalgleish" wrote:


In the formula:

=OFFSET('SalesPivot'!$A$1,0,0,COUNTA('SalesPivot'! $H:$H)
-COUNTA('SalesPivot'!$H$1:$H$10)+10,11)

SalesPivot is the name of the worksheet, and cell A1 on that sheet is
used as a reference point.
From there, the offset is zero rows and zero columns as a new starting
point.
The number of row is determined by counting the items in column H, so it
is assumed that there is an entry in every row of the pivot table, in
that column.
There may be headings in H1:H10, so those are counted, and subtracted
from the count of items.
Then, 10 is added to the count, because there are 10 rows in the heading
area.
The result of the counts should equal the last row in the pivot table.

Ekazakoff wrote:

Thanks Debra but I couldn't make this work. I didn't have a grand total on
my data so I did add that thinking that per your suggestion the grand total
row would be the final row of the print area. However I don't understand the
offset formula you have written. You use h10 in your formula dn then you add
10. Does this imply that you need to know the row length of the report? I
will have at least 8 users each having a different report length. Any
clarification would be appreciated.

"Debra Dalgleish" wrote:



You can define a dynamic print area for the worksheet:

Select the pivot table worksheet
Choose InsertNameDefine
In the Names in Workbook box, type: 'SalesPivot'!Print_Area
using your sheet name instead of SalesPivot
In the Refers to box, type a formula based on a column that has a value
in every row, e.g. the Grand Total column:

=OFFSET('SalesPivot'!$A$1,0,0,COUNTA('SalesPivot'! $H:$H)
-COUNTA('SalesPivot'!$H$1:$H$10)+10,11)

In this example, the values start in cell H11, and the print area
will be 11 columns wide.

Click the Add button
Click OK

Ekazakoff wrote:


I have added an extra column to a pivot table to analyze the results. The
pivot table is set up with a page filter for separate users. Each user can
select their data to review without having to look at the entire data set
which is 240 pages. I have used if statements so that the formula rin the
added column runs only if there is data. However if the user selects page
preview they still think their report is 240 pages long because of the
formulas in the added column. Is there any way to avoid this?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html