Closed Thread
Results 1 to 5 of 5

pivot table counting blank cells in data table

  1. #1
    Forum Contributor
    Join Date
    06-30-2008
    Location
    london
    Posts
    107

    pivot table counting blank cells in data table

    Hi all,

    I have a pivot table which is counting blank cells from the range of data that the pivot table is reading from.

    I have been able to fix this by putting on a filter in the data table, highlighing all the blank cells only and then clearing them column by column.

    The problem is the data table is 50 columns long, so it is quite time consuming to do this manually. The data table gets updated regularly so this is an ongoing problem.
    Does anyone know of a faster solution to fix this problem?

    many thanks,

    Nicko

  2. #2
    Forum Contributor
    Join Date
    07-10-2007
    Location
    Portland, OR USA
    Posts
    135
    Try checking you pivot table settings... could be your row or column fields are set to include blank cells.

    Also check the formatting of the "blank" cells that are being pasted into the data range.

    Post a sample .xls and this will get resolved in no time.

  3. #3
    Forum Contributor
    Join Date
    06-30-2008
    Location
    london
    Posts
    107
    here is an example of of my problem...

    the pivot table is picking up every cell

    any ideas?

    many thanks,
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    07-10-2007
    Location
    Portland, OR USA
    Posts
    135
    Ok.. I see now. You are geting these results because you are counting Severns, Fens, & Cullum. You can't count those because they are column headers, not data. The items under these headers is what is being counted: AMBROSE, AMION, etc.

    What end result are you trying to achieve? Is it that you want a count of Severns, Fens, & Cullum by Primary Category regardless of the data in this categories?

  5. #5
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    1. Stick to your original thread.
    2. Don't cross post.
    3. If a moderator makes a remark in your thread don't just ignore it and open a new thread somewhere else.
    Your original thread is here :
    http://www.excelforum.com/excel-gene...continued.html

    You have cross posted here :
    http://www.vbaexpress.com/forum/showthread.php?t=22404

    It would be polite of you to place a link on VBAExpress to your post here, and at any other sites you might have posted at rather than waste others' time.

    You might also like to read this.

    Thread closed.

    DominicB

    Edit : ...and here
    http://www.ozgrid.com/forum/showthread.php?t=108709
    Last edited by dominicb; 09-23-2008 at 03:37 PM.
    Please familiarise yourself with the rules before posting. You can find them here.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot table via external data sourse (cvs)
    By JoannaP. in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-17-2008, 01:53 PM
  2. Replies: 1
    Last Post: 09-16-2008, 04:11 PM
  3. Extracting Data from a Pivot Table and comparing
    By mwburne@yahoo.c in forum Excel General
    Replies: 1
    Last Post: 10-19-2007, 08:31 AM
  4. Pivot table: repeat row data in all cells
    By jmwismer in forum Excel General
    Replies: 0
    Last Post: 02-25-2007, 05:44 PM
  5. eliminate the blank total in my pivot table
    By carsto in forum Excel General
    Replies: 3
    Last Post: 11-30-2006, 04:19 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1