So I have a report that outputs to EXL07 FORMULA that has an ACROSS in it. I'd like the ROW-TOTAL option to function correctly but it does not. It throws a #VALUE! error whenever there's a missing value in the row, even when I set a NODATA.
Here's the rub -- it works fine when the sheet first opens. I only throws the error when you save the output or click the Allow Editing option in Excel.
Here's the question -- Has anyone bumped into this and found a solution? It more or less works correctly in EXL2K, but it's ugly and doesn't respect the NODATA option in the display. This appears to be a legit bug in the EXL07 FORMULA output option, and I'd like to know if anyone has seen a workaround. I can't find it in the forums.This message has been edited. Last edited by: FP Mod Chuck,
You need to set NODATA=''
From the docs: NODATA With Formulas
SET NODATA = '' TABLE FILE CAR SUM SALES/I11C AS '' BY CAR ACROSS COUNTRY AS '' ON TABLE ROW-TOTAL ON TABLE PCHOLD FORMAT EXL07 FORMULA ON TABLE SET PAGE-NUM NOPAGE END -RUNThis message has been edited. Last edited by: Hallway,
That works! My clients want zeros in the empty fields, and that fails. It appears they will need to make a choice.
It really doesn't make much sense why the NODATA is inserted as text, since most null value fields are in COMPUTES.
IMO there should be the option to enter a zero and have it be an actual 0. Maybe something like this:
-* INSERT A ZERO AS AN INTEGER SET NODATA = 0 -* INSERT A ZERO AS TEXT SET NODATA = '0'This message has been edited. Last edited by: Hallway,
I tried both of those and both worked in the cells, but neither worked in the row-total formula.
|Powered by Social Strata|