Forum Discussion

RobertWressell's avatar
RobertWressell
Qrew Trainee
19 days ago

XL Docs breaking all references

We are just beginning to play with the XL Docs extension. I've got a subtable that is populating data on sheet 2. When I generate the doc the data appears there as desired, but on sheet 1 we have a collection of formulas calculating things like % complete. The issue is any formula that is set to count or otherwise reference sheet 2 changes. Instead of referencing the column on sheet 2, as it does in the template, it breaks and we get a #REF! error. I can manually rebuild the formulas after generation, but at that point it would be faster to download a report of the desired data, then copy & paste into the sheet with working references.

XL Docs shouldn't be doing anything at all to sheet 1, so why does it break the formulas there? The subtable on sheet 2 starts at row 2, so the headers are completely static.

Extensions

1 Reply

  • RobertWressell's avatar
    RobertWressell
    Qrew Trainee
    19 days ago

    I have found a workaround. Turns out Excel doesn't like that rows are being added by XL Docs when he sheet is generated. For whatever reason, even though the formula was set to count the entire column anyway, adding rows only works if you make the starting of the column reference static. So =COUNTIF('MASTER LIST'!$F:F,C2) Only sticks around with the $ on the first F. Any formula on Sheet 1 that does not reference sheet 2 still works.