Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I have a spreadsheet that I leave "auto filtered" in each column. I only have about 4 months of information and already it's about 300 rows. As the year goes on, it may be harder to find information. It is sorted by vendor name and I can scroll down to find what I need but it is taking longer and longer however.

    If I want to look at just the vendors whose name begins with "S" for example, I use the filter and then Custom, begins with.....etc. I do this for each letter I'm interested in.

    Is there a faster way of doing this? I really would like something similar to what you can do in Access where I had a "toolbar" where I just click on the letter and got the results. I really don't want to do a ton of macros but maybe that's the only way?

    Any thoughts?
    Thanks!
    Louise

  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
    You could create a macro with a toolbar and have it do the filtering. You could add each letter and have the macro set the filter accordingly.

    Without code you could just create a column containing the first letter of each item in the column (hardcoded or with a formula) and then filter on this column instead of the other.

    Steve
    PS
    Another option which does not involve creating buttons for each letter is to use a scrollbar from the forms toolbar. Set the min and max to 1 and 26 respectively. You can set the celllink to something like B1 (you do not need to display you can cover it with the toolbar) and then in another (visible) cell close to the scrollbar (like A1) use a formula =Char(64+B1) to display the letter corresponding to 1-26 (A-Z). You can create a macro to set the filter based on the value in A1 and assign it to the scrollbar. Then when the scrollbar moves you see the letter and you stop and it is filtered.

    See attached.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve,

    On the toolbar idea, wouldn't the toolbar have to be visible all the time, even if I'm not on that particular spreadsheet? I probably could hide it and / or apply it every time I need it. Thinking out loud here.

    Good thoughts! Thank you!
    Louise

  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
    I wasn't thinking of the literal XL toolbar, more a series of 26 custom macro buttons at the top of the sheet....

    (Have a look at the PS I added to my previous reply) since you responded before I added it and the example with a scrollbar.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve,

    Looks like we were posting at the same time!

    Wow! That looks neat! Never knew about the forms toolbar before. See? Learn something new every day! Will have to learn more about it.

    That looks really great and works well too. I think I'll give that a try!

    By the way, how do you move it? or can you?

    Thanks again,
    Louise

  6. #6
    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
    Here is an example with a scrollbar and 26 separate buttons. The Buttons all use the same macro, it reads the text from the button and filters on this. This would allow filtering on longer items. that you use alot.

    I forgot in the original to include the "begins with" and this corrects it in both of routines...

    Steve
    Attached Files Attached Files

  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
    [quote name='weese237' post='779041' date='09-Jun-2009 18:44']By the way, how do you move it? or can you?[/quote]

    Right click it, and it will be selected. Then you can drag it where you want on the sheet

    Note my later reply where I added another sheet with the 26 buttons and the ability to add Custom buttons.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve,

    That looks great!!! Boy I've got some learning to do!

    Looks like those buttons are from the forms toolbar as well? Will try to figure it out using your excellent example. I'll probably have more questions.

    Thanks again!
    Louise

  9. #9
    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
    [quote name='weese237' post='779044' date='09-Jun-2009 19:09']Looks like those buttons are from the forms toolbar as well?
    I'll probably have more questions.[/quote]


    Yes they are the "Button" from the Forms toolbar, each set to the same macro. The macro filters on the column with anything that begins with whatever is on the button.

    I am glad I could help. If you need additional help don't hesitate to post a question...

    Steve

Posting Permissions

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