Dynamic print range on a pivot table

Resolved · High Priority · Version 365

Thu 27 Jan 2022, 15:12 Delegate Valerie said...

Valerie has attended:
Excel Advanced - Formulas & Functions course
Power BI Reporting course

Dynamic print range on a pivot table

I would like my print range to vary with a pivot whenever it is refreshed.

I am trying to set a dynamic range on a pivot table using:

OFFSET(Sheet1!$A$3,,,COUNTA(Sheet1!$C$3:$C$2000),COUNTA(Sheet1!$A$3:$Q$3)

to set a named range, and then referencing this from the Print_Area name.

It's half working in that it seems to adjust to the length of the pivot table as that changes, but not the column width.

Any ideas?

(Also is there any easy way to make sure all cells in the pivot table data source have the same format, as my pivot table is treating some 'years' as numbers and some as text, which is messing it up a bit.)

Thanks.

Thu 27 Jan 2022, 17:13 Trainer Jens said...

RE: Dynamic print range on a pivot table

Hi Valerie,

Thank you for the forum question.

Try:

OFFSET(Sheet1!$A$3,1,,COUNTA(Sheet1!$C$3:$C$2000),COUNTA(Sheet1!$A$3:$Q$3))

About years formatted as text. I do not know where you have the problem, but to me it sounds like (if the problem is in the source data) Power Query can do the job.

Power Query can do all kind of cleanings tasks.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 28 Jan 2022, 08:53 Delegate Valerie said...

RE: Dynamic print range on a pivot table

Hi Jens

Many thanks for your very prompt reply.

I think I partially solved my problem - it was related to the text in a column extending beyond the column, so when I wrapped the column, the dynamic range did then work.

However, it is still not doing quite what I expected with the rows eg I wanted to start the print range at row 3, and I tried
OFFSET(Sheet1!$A$3,,,COUNTA(Sheet1!$C$3:$C$2000)-1,COUNTA(Sheet1!$A$3:$Q$3)
to try and miss the final row of the pivot table.

But the print range still extends from Row 1 to the bottom of the pivot table.

Training courses