Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Report List with No Blank Cells (Excel 97)

    Sorry if this has been answered already....How long 'til I can search again????

    I have a list of employees. In that list only certain ones meet a specific criteria. I have already identified against my list of 30, a COUNTIF function to return a 1 if the criteria has been met. On a different sheet, I want to list only the ones that meet this criteria. I don't want blank spaces in between each one that returns TRUE. How can I create this list? See attachment for expected results...

    thanks in advance
    christine
    thanks
    christine

  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: Report List with No Blank Cells (Excel 97)

    Use an advanced filter (Data -filter -advanced filter)
    1) put a heading on your 2 columns (eg A5 = "A", B5 = "B"
    2) create a criterion using col A heading in another location. eg Cell G5 = "A". Underneath this (Cell G6) put a zero (0) since you want to extract the ones that a = zero
    3) Create the out put range put the column headings you want (eg in I5 put "B" to output column B.

    Now we will extract the cells in Col B that have "0" in col A:
    Select a cell in col A or b in the data range
    select data-filter - advanced filter from the menu (you whole range will be outlined)
    Pick "copy to another location
    Criteria range = G5:G6
    Output range = I5
    Check "unique records only" if you want no duplicates
    <OK>

    You can also use the filter in place or even autofilter to filter in place and then copy and paste the range elsewhere
    Steve

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report List with No Blank Cells (Excel 97)

    Thanks for the help. I do have another question though. The source of my data is a query to a database with a work date parameter that is setup to refresh on open. Will this advanced filter automatically adjust at each update?
    thanks
    christine

  4. #4
    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: Report List with No Blank Cells (Excel 97)

    When you do the adv filter after the query it should reset to the "CurrentRegion" of the database, so it will expand and contract as data is added/removed

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report List with No Blank Cells (Excel 97)

    So I just gave it a shot. Properties set to update on file open. Changed the criteria to return different data. The advanced filter did not update. I had to manually refresh the filter. So, is the best I can do a macro button (or something) that will enable the report viewer to see updated data from the filter?
    thanks
    christine

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report List with No Blank Cells (Excel 97)

    could you not just build another query using your new criteria for the second list?

    Peter

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report List with No Blank Cells (Excel 97)

    Hey, hey now.....we can't do things the smart way! Come on, if it's not something that would go around in circles and waste time, why do it??????

    So I'm trying to get a subtract join setup and that won't work either! I just can't win on this thing!!!!!
    thanks
    christine

  8. #8
    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: Report List with No Blank Cells (Excel 97)

    I misunderstood your question. I thought you were asking if the DATALIST would be updated (the SOURCE list). The output list (as you discovered) is "static". It is a set routine to "filter and copy and paste" that excel runs. It must be rerun (though a macro could do it easily).

    You could do it with an ARRAY formula to make it dynamic but you would have to adjust the size to be large enough to cover the "database" max rows. It could get sluggish if it gets too big:
    Enter this formula in a cell in row 6 (eg I6)and confirm with control-shift- enter:

    =IF(ISERROR(SMALL(IF($A$6:$A$37=0,ROW($A$6:$A$37), ""),(ROW($A$6:$A$37)-5))),"",INDEX($B$6:$B$37,(SMALL(IF($A$6:$A$37=0,RO W($A$6:$A$37),""),(ROW($A$6:$A$37)-5)))-5))
    [note the 5s in the formula are because you started in row 6]

    Then hit <F2> and HIGHLIGHT I6:I37 and again confirm with ctrl-shift-enter.

    This list is LIVE. Change the 0s and 1s and your list will update! You can Expand the list by moving A37:B37 to another row, then repeating the <F2> and expanding the range from above. You will NOT be able to insert rows due to the ARRAY in col I.

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report List with No Blank Cells (Excel 97)

    Thanks for the clarification. I just wrote a quick macro and added to a button on the report. The user can manage to click ONE more thing, I think! But I will keep your array formula handy.

    thanks again
    christine
    thanks
    christine

Posting Permissions

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