I have a template with Row ranges and a column range. Although FlexCel doesn't complain, the column range actually intersects with the parent row range. One of the nested row ranges is an X range. It was working fine until I added the column range. It appeared that the blank row deletion and the column range do not get along. I would like to send the source code including the template. But I don't see an option here for that. Please advice. I am attaching pics of both the template and output.
Reports where ranges intersect can have indeed some cases where they don't work. Can you send me the template/result you are getting to adrian@tmssoftware.com so I can take a look?
Thanks for the files, I got them. I am not really sure I understand how the X ranges are working though.
In your original email, you spoke about one nested range being X, and I think this should be the case, but the range that should be X in this template is Sector which should be __Sector__X
In the attached template, the outer ranges are X, and sector is not. This will mean that the rows 12 and 10 in the template will be deleted, but the empty row below sector won't because Sector is not __Sector__X.
Can you retry with the correct X ranges (making __Sector__X and all the others not X) and send me the new results if they aren't correct?
Also make sure that it is __Sector__X (2 underscores) and not _Sector_X. In your original screenshot ( https://prnt.sc/fpi6nx ), it looked as if you were using a Sector range. With a single underscore, only the actual A6:D6 range will move down because Sector is only A6:D6. This seems to be what happens in the screenshot, after column D the report moves up. But this shouldn't happen if it is a double underscore range SectorSector should be the same as if you had selected A6:XFD6 for sector.
I shall send you the template with one X range. But that is not really what we want. We have nested row ranges and all of them have sub totals. Classification, supersector and sector. So we want 3 X ranges. This was all working until I added a new column range on the right side II_Account_II.
The template I sent you, I had removed the X from __sector__. I did that because, the output of that made sense except the blank rows. So my objective is to remove them. Please let me know if you still need the template with only one X range which is __sector__X. The output will look confusing!
That's fine, I didn't realize that rows 9 and 11 were hidden, that's why I thought supersector and classification couldn't be X ranges (you always need a blank row below an X range). But now I see that this should be ok.
But I think the file you sent me (without the __Sector__X range) is fine (except for the empty lines)? So the problem would be when you make Sector to be __Sector__X ?
If that is the case, let me check what could be going on. Just to make sure, what happens if you make __Sector__X to be A6:XFD6 instead of A6:D6?
Currently the __sector__ range is =Sheet1!$A$6:$D$6. I tried Sheet1!$A$6:XF$D$6 like you suggested. But it wouldn't let me. Excel didn't like it. Then I tried Sheet1!$A$6:X$$D$6, Sheet1!$A$6:$X$F$D$6 with no luck.
It is Sheet1!$A$6:$XFD$6 (in xlsx) or Sheet1!$A6:$IV6 (in xls)
Bu you could also do Sheet1!$6:$6 which will work the same in both xls and xlsx.
Or just select the full row with the mouse :)
Basically, A is the first column in an xlsx file, XFD is the last. An xls file has less files, so IV is the last for xls. The $ are to make the references absolutes, which is needed in names or they would move depending in the row you are in.