Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    pivot table of only visible data (Excel 2000)

    I would like to create a pivot table of just visible data. I have data in cells below the visible data, they just happen to be formulas, and unseeable until other data is entered and then the formulas calculate and are visible pieces of data.
    I've tried using the offset command for the Pivot Table Range for e.g.

    =OFFSET($A$2,0,0,COUNTA($A:$A),1)


    But I know this is going to have to be tweeked in order to see all visible data from a2 through the last visible data in column J...Can anyone help me on this?
    Thanks
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: pivot table of only visible data (Excel 2000)

    Are you looking for:
    =OFFSET($A$2,0,0,COUNTA($A:$A),10)

    This will get all the data based on the total number of pieces of info in col A. To work column A must have not internal blank cells (or the count will be wrong)


    Though it has nothing to do with visible data...


    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pivot table of only visible data (Excel 2000)

    That seems to work just fine.
    Thank you so much [img]/forums/images/smilies/smile.gif[/img]
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pivot table of only visible data (Excel 2000)

    whoops, not quite...it is not grabbing the next new line of data, as refreshing the pivot table does not show the new data at all.
    well, got to take a break...if any suggestions on this...please add them
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: pivot table of only visible data (Excel 2000)

    Can you create a sample file demonstrating the problem and attach it?

    It will help us to troubleshoot.

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pivot table refresh dynamically

    I am including the spreadsheet. As you will see, when I add new information on the first sheet...eg. Betty Casey, and then go to the Pivot Table sheet and refresh the data, Betty Casey RN is not included in the refresh. What I want the Pivot table to do is dynamically pick up the new data entered into the spreadsheet and show in the Pivot table upon clicking the refresh button on the Pivot table tool bar. I keep putting in the formula of Offset that I was given in this thread as the basis for the Pivot table, but when I go check, it reverts back to A3 as the pivot table range.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: pivot table refresh dynamically

    You do not have the pivot table source based on the range name. It is a hard-coded range.

    Create the name ClassList (or whatever):
    Refersto:
    =OFFSET('Instructor & Class Info'!$A$1,0,0,COUNTA('Instructor & Class Info'!$A:$A),10)

    Then use this named formula (=ClassList) as the pivot table source range

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pivot table refresh dynamically

    You were right of course...no range name defined. And of course it now works beautifully. Thank you a ga-zillion for helping me through that as it was giving me a fit. [img]/forums/images/smilies/smile.gif[/img]
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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