Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fill series in filter mode (excel 97)

    In the filter mode, how can I fill series on the visible cell only.
    for example, row 2 and row 3 is filtered, and put 210 on c1, try to increment by 1 to 219 on the visible cells only.
    thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill series in filter mode (excel 97)

    - Filter the list
    Enter this formula next to it:
    =SUBTOTAL(3,A2:$A$2)+209
    Copy down.
    Select the formula's, copy, paste-special, values.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill series in filter mode (excel 97)

    Pieterse
    thank you for the help.
    I am still struggling with this formula.
    1) when I return to the unfiler mode the value will change. I tried to copy and pastspecial value only in filter mode, It won't work.
    2) I want to insert a name for that formula (something like Xfill=subtotal(3,a2:$a$2) and I sometimes need to put this starting from c10 or some other cell, then the formula will not work right. Any suggestion will be appreciated.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill series in filter mode (excel 97)

    Hi,

    <<1) when I return to the unfiler mode the value will change. I tried to copy and pastspecial value only in filter mode, It won't work.>>

    It worked for me!
    Was my description unclear?

    <<2) I want to insert a name for that formula (something like Xfill=subtotal(3,a2:$a$2) and I sometimes need to put this starting from c10 or some other cell, then the formula will not work right. Any suggestion will be appreciated.>>

    Assuming the original formula does work when used from cell C10 and on...

    Make sure you're on cel A2 when you define the name, otherwise the A2 without dollar signs will be adjusted *relative* to the cell you are on during the definition.

    Example: If you define this name : OneLeft, refersto: =A1 Whilst on cell B1 and you use the name in cell S1 (=OneLeft), the name will refer to cell R1 (one column to the left).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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