Help with removing a value (or entering zero) ONLY if a referenced cell indicates "Not attempted"

[smatterchu]

I'm helping someone on a series of spreadsheets she does every month/quarter for dozens of different groups. Each group is slightly different, so it's not as easy to standardize. I'm trying to help her consolidate her efforts where possible, so I'm hoping to avoid manual manipulation x's 75 workbooks...

Okay - I'm working in Office 2010

The issue is this. I have a report that lists whether a person's training in a course is "Complete," "Incomplete," or "Not attempted." After having a pivot table count each instance, I only want to show a count/1 for the "Complete" or "Incomplete" courses.

I still want to keep the "Not Attempted" records because they show what specific courses/exams are still left to take for that student, but I don't want to affect the tally/total. If we sorted and removed from the data before doing the pivot table (removing them from that tally), the associated courses will also disappear and not be linked to that student. We need the courses there so managers know what else is needed to be attempted.

How can I hide (or replace with zero) the cells on the row with "Not Attempted?" Otherwise, how to I make an equation that ignores those other records?


Here is a simplified example of the info:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]EXAM 1[/TD]
[TD]EXAM 2[/TD]
[TD]EXAM 3[/TD]
[TD]EXAM 4[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]REGION 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Complete[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Incomplete[/TD]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Not Attempted[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]REGION 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Complete[/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Incomplete[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Not Attempted[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So, I've got to figure out a way to keep the Complete and Incomplete values intact while hiding/removing/0'ing the Not Attempted values. Also, as you can see, there are other rows that don't have any values -- they just need to be ignored.

This is just one of many workbooks that will need updating, so I'm hoping someone can suggest a way that is fairly simple to duplicate.

If this has to be done in VB, I can try to figure out/remember how to do that. It has been nearly 15 years since I have played around too deep in Excel. I used to have more of a head for it, but I'm slowly re-learning...

Finally, thank you all for your efforts and support on here. I found you by accident trying to research a few questions on YouTube. While I am learning a lot, I'm still enough of a beginner that I'm having trouble transferring the information I'm taking in to my specific question.

Please forgive me if you have answered something similar on here. It has been a very long time since I worked in Excel, so I'm having to re-learn (and un-learn) a fair amount.

Thanks again!!

 

[dspel79082]

Hi Smatterchu, welcome back to Excel
There really isn't any direct and easy way to alter data once it gets into the pivot table. What I would do would be to add a column to the source data. You can hide the column if you like, the pivot table will still pick it up.
In that column you can put a formula that gives a value to the items that you want to have a value. Something along the lines of =if(a1="Complete",1,if(a1="Incomplete",1,0)). Then inside the pivot table set that field to sum instead of count ( in the field settings ) and you will have a field that will produce a sum of completes and a sum of incompletes and 0 for anything else.
You could approach this several ways but I believe this would probably be the easiest and most direct.

 

[smatterchu]

Wow, thanks so much!! I will definitely give this a try!! I gave up on trying to deal with the pivot table and was thinking I would need to manipulate the spreadsheet the was using after having made the pivot table. I hadn't thought that I could manipulate the source date to affect the table.

Wonderful!! I really appreciate your help. I'm going to do this first thing in the a.m. and let you know how it goes. ((happy dance))

 

[smatterchu]

Thank you again!! I couldn't wait to try it -- it works!! I wound up putting together a lookup table, because beyond 2 references, and I'm lost, lol...

I just made a little 2x3 table on the next sheet with the numbers next to the status. I referenced that table in the column, and it totally worked!! Yay!

=VLOOKUP(P2,Sheet2!$A$1:$B$3,2)

And you're right -- it shows up correctly in the pivot table. I'm going to let her try and make sense out of how she wants it sorted/tabbed...

Thanks again for your help!!