Forum Discussion
DHR1982
Jan 13, 2021Copper Contributor
Conditional page break position
Hi Is it possible to set page breaks in Excel to automatically adjust to a specific row/column depending on which rows/columns are visible (so not hidden) at the time? I cannot seem to make this ...
DLSMIC
Jul 29, 2022Copper Contributor
DHR1982, I am struggling with a similar same problem. I have tables with about 1000 lines and I want to introduce a page break between groups with up to 40 lines each. Everything should be dynamic. I would love to see page breaks in conditional formatting. For now I can share my work around:
Lets say Table1 is the Table that I want to split over multiple pages. In a new sheet I insert the following formula:
=LET(pagesize;49;cols2copy;3;
groups;UNIQUE(Table1[Group]);
numberofgroups;ROWS(groups);
rowsingroup;MAP(groups;LAMBDA(x;COUNTIF(Table1[Group];x)));
groupoffset;XMATCH(groups;Table1[Group];0);
groupstart;SCAN(1;groupoffset;LAMBDA(a;x;CEILING(a+x-2;pagesize)+1));
groupend;MAP(SEQUENCE(numberofgroups);LAMBDA(x;INDEX(groupstart;x)+INDEX(rowsingroup;x)-1));
totalrows;INDEX(groupstart;numberofgroups;1)+INDEX(rowsingroup;numberofgroups;1)-1;
MAKEARRAY(totalrows;cols2copy;LAMBDA(r;c;LET(group;XMATCH(r;groupstart;-1);rowintable;r-INDEX(groupstart;group)+INDEX(groupoffset;group);IF(r<=INDEX(groupend;group);INDEX(Table1[#Data];rowintable;c);"")))))The idea is that each page will have the same number of rows (49). So it does not work if there are cells with line breaks.
Is there a better way to do this?