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