Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

What combination of techniques with cell formulas and pivot table configuration will enable the drill-down into the pivot table to only show rows containing the 'desireable' valued cells, ignoring the rows whose cells aspire to be empty?

I've made a fair effort to decouple my question from the solutions I've tried and focus the attention on the exact issue - pivot tables (and drill-down thereinto) behave differently with truly empty/clear/void cells than otherwise, and I can find no other 'value' that a formula can return that the pivot will treat identically to a cleared cell.

I've seen MS Graph folks meet with success using na() as the formula return for the cell and have the graphs ignore the cell, but cannot find anything that pivots will similarly ignore.

As we know, if(condition, affirmative_string, "") does NOT work, as the pivot table counts the empty string the same as one with length. Changing to if(condition, 1, 0) and configuring the pivot to summarize-by-sum is similarly not an acceptable alternative as the drill-down shows all rows contributing to the sum, even those with cells containing zero.

I've tried a UDF returning all the variations I know of for CVErr(), as well as trying to return those kinds of values as strings. I'd craft a UDF to throw a particular exception if Excel would react by clearing the cell's value (not the formula). [I'm not able to conjure an answer for why Excel wouldn't always empty the value area immediately prior to executing a formula whose interpretation was error-free, which would then enable the use of an exception-based approach, but we could all grow old trying to figure out why MS does what they do.]

Telling me a cell containing a formula is by definition not 'empty' is not constructive; Excel knows the difference itself or we wouldn't be able to have cell formulas operate on other cells whose value came from a formula. Even in the OM you can see that it stores the formula string separately from the result value.

The suggestion to manually clear/empty the cells whose formula return is the 'ignore me' value is a workaround that provides the correct pivot table values and drill-down behavior, but is not a 'solution'; it sacrifices the underlying formula and the workbook's dynamic nature to get there.

In my mind, a builtin function along the lines of "null()" would have been exactly the solution, but I can't find anything that even accidentally behaves that way.

share|improve this question

migrated from stackoverflow.com May 20 '13 at 3:30

This question came from our site for professional and enthusiast programmers.

    
Have you tried if(condition, something, #N/A)? –  Scott May 21 '13 at 16:43

1 Answer 1

Just a thought, but maybe a workaround you could investigate is to build two VBA subroutines that you can call before and after drilling down into your pivot table. The first subroutine searches through your pivot data for a value that indicates the formula needs to be cleared. It then logs the address of the cell and its formula on a separate (hidden) sheet. Finally it deletes the formula from the cell.

After drilling down into your data, you could run the reverse subroutine which will read from the log on the hidden sheet, repopulate your pivot data with its original formulas, and clear the log.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.