Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Altoona, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel pop-up filter

    I recently received a spreadsheet from Europe that had certain columns in which clicking on any cell in that column caused the drop down control to appear to the right of the cell (looks similar to the "auto-filter"). A list was displayed who's contents came from a range of cells in a different column. You could enter any item from the list into the cell by selecting it. I have always wanted to do exacly this but did not think it was possible. Does anyone know how is this done? I've gone through the help file at length but to no avail!

  2. #2
    ruaridh_o'likely
    Guest

    Re: Excel pop-up filter

    Hi Jason,
    I think what you're after is the data validation feature (Data-Validation...on the menu) - you need to set Allow to List and have In-cell dropdown checked.
    HTH.

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Altoona, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel pop-up filter

    Yesss! that is "it" exactly. Thanks ruaridth_o'likely. I feel a little humble, especially as I have used data validation before, but never in that way.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel pop-up filter

    Ruaridh,

    I never realised you could do that! Thanks for the tip.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    ruaridh_o'likely
    Guest

    Re: Excel pop-up filter

    always glad to add my few drops to the pool of knowledge[img]/w3timages/icons/grin.gif[/img]

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel pop-up filter

    And you can use the Data >> Validation tools in a different way too. Suppose you have a data set and want to check if the data in a column (or range of cells) belongs to a list (or is between a predefined minimum and maximum), then select that column, and set the validation rules via Data >> Validation.
    Then choose Tools >> Auditing to make the Auditing toolbar visible and click the circle invalid data button. All mistyped or erroneous cells will be marked with a red circle. Very handy to validate your data after entry.

  7. #7
    cwoodybutler
    Guest

    Re: Excel pop-up filter

    Just to verify - there's no way to use the Data Validation functions with the data validation "list" on a separate worksheet within the same workbook?

    --woody

    C. Woody Butler
    cwbutler@cebec.com

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel pop-up filter

    Define the list as a named range and then refer to the name in the Data Validation List box (I'm doing this in Excel8 - I would assume it would be in Excel9).

  9. #9
    cwoodybutler
    Guest

    Re: Excel pop-up filter

    Check it out - it worked!!! Gee, I'm gonna look like a genius to my users! [img]/w3timages/icons/thinks.gif[/img]

    Now that I know how it works, I can see it DOES actually say that in the help [img]/w3timages/icons/spook.gif[/img]...

    Thanks a bunch -

    --woody

  10. #10
    cwoodybutler
    Guest

    Re: Excel pop-up filter

    no, I was wrong - it still doesn't work cross-worksheet.

    ARGH!! [img]/w3timages/icons/dragon.gif[/img]

    --woody

  11. #11
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel pop-up filter

    It works 'cross-sheet'. I have a large workbook that has a "Input Parameters" worksheet where I have the users enter certain choices in defined ranges. In several other worksheets, I've used Data Validation to limit choices and present a pull-down for cells that are based on these 'Input Parameters'. The data validation list on all of the sheets refers back to the named range on the "Input Parameters" worksheet. The syntax for the entry in the Data Validation 'List Box' is "=Defined_Name" (without the quotes) where Defined_Name is the range name given to the list area. This is a workbook level name in my case. If it is a worksheet level name it would look like =Sheet_Name!Defined_Name. You may need quotes for names with spaces. The easiest thing to do is to choose the list box and then go to the worksheet where your defined range is and use Insert Name Paste to insure you get the proper format.

  12. #12
    cwoodybutler
    Guest

    Re: Excel pop-up filter

    arrrgh - somewhere in me playing with things I lost my equal sign in the formula.[img]/w3timages/icons/sick.gif[/img]

    Yes - it works, I'm back to being a genius again! Thanks![img]/w3timages/icons/thinks.gif[/img]

    --woody

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel pop-up filter

    My Curling scores now have a new feature for the score keeper! I love this lounge! Thanks for the tip!
    Christopher Baldrey

Posting Permissions

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