Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofilter showing particular cell (Excel 97)

    Hi all,
    I am stuck at the following problem. I have a small table (pls. see attachment) and below it another listing of brands and in the next column numbers, of which I made an autofilter. The result of the autofilter will be always one row, and what I want to achieve is that the number (in column [img]/forums/images/smilies/cool.gif[/img] which is visible from the autofilter to be shown (linked or otherwise) to the cell B19 (shown in yellow) in the table above.
    I cannot link the number to the table because every time there can be a different row that is visible, depending on what has been selected in the filter.
    Could anyone help me get this result, as I have no idea what method to use for it.

    Thanks a lot,
    K.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autofilter showing particular cell (Excel 97)

    I have inserted a formula into B19 which either takes the filtered value, or if the filter is not in place takes the first value (B29). File attached.

    Does that give you what you need ?.

    Andrew C
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter showing particular cell (Excel 97)

    Thank you very much, it is EXACTLY what I wanted.

    Cheers,
    K.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofilter showing particular cell (Excel 97)

    An excellent reminder that the Subtotal function ignores hidden rows. Subtotal is such a useful function; no wonder Microsoft made it so cryptic. <img src=/S/bash.gif border=0 alt=bash width=35 height=39> I would prefer the following similar formula for B19
    <pre>=IF(SUBTOTAL(2,B29:B38)=1,SUBTOTAL(9,B29:B38) ,"No Chain")</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autofilter showing particular cell (Excel 97)

    Well that unfortunately gives some nasty looking VALUE# errors in all the other cells in the table.

    Andrew C

Posting Permissions

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