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

quote:

The null value (NODATA='') is supported for calculations. When cells containing the default NODATA symbol (.) are used in a formula, they will cause a formula error.

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

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

Thanks!

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.