Solved

conditional statements in pivot table calculated items

Posted on 2014-01-10
1
676 Views
Last Modified: 2014-01-22
the attached spreadsheet includes a pivot table with calculated items, difference between Critical and TOA.  If both the Critical and TOA are blank, I don't want to take the difference, this way the rows won't be created.  When I minimize the rows (column A) by clicking the minus (-) sign, the sheet recalculates the formulas.  It's not too bad on this sample sheet, but takes almost 5 minutes on my complete sheet.  Within the calculated item I tried, if(and(Crititcal <>"",TOA <>""),Critical '-'TOA,"").  However it didn't work and I'm wondering if I'm able to use an If statement within the calculated item?  Is it possible to write a macro or VBA to collapse the rows with no data?
ColumnDifferenceSamplev2.xlsx
0
Comment
Question by:jvantassel1
1 Comment
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
Hi

You could use a filter in your rows to hide Rows with Zero

In Your example click A7 to Select the APPN Rows
Right-click / Filter / Value Filter /Sum of resource Amount "is" not 0

Regards
ColumnDifferenceSamplev3.xlsx
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now