Results 1 to 6 of 6
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Visible cells named ranges (Excel2000)

    Hi All

    Is it possible to assign a range name to visible cells only??

    I have a table of records.
    I can use autofilter to select a subset of the records.

    What I would like to do is have some range names assigned to some of the columns of visible data so that I can use them in some array formulas.
    For example I would like to assign the name colZ to the (filtered) visible cells in column Z, , colAB to the visible cells in column AB etc preferably excluding the heading row.

    In the meantime what I've been doing is using AdvancedFilter Copy to another location to extract the filtered records I want to another worksheet and then creating the range names there.

    Is this the only way????

    zeddy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Visible cells named ranges (Excel2000)

    I don't think you can create a named range that refers to visible cells only. In some situations you may be able to use the SUBTOTALS function.

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visible cells named ranges (Excel2000)

    I think you may be able to actually create the named range, but may not be able to use it in an array formula.

    1- Filter to the visible cells desired
    2- Highlight/select the cells
    3- Press alt + ; (ALT PLUS SEMICOLON or Edit | Goto | Special | VisibleCellsOnly)
    4- Type the desired range name in the name box
    5- View the result from the Insert | Name | Define window

    If you selected non contiguous cells you will see several ranges seperated by commas in the Refer To window

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Visible cells named ranges (Excel2000)

    Many thanks for your observations.

    I'll investigate further but guess I'll have to continue with a filter-extract process and then apply names to the results.

    regards

    zeddy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Visible cells named ranges (Excel2000)

    I think Zeddy wanted to define a name that would refer to visible cells dynamically, i.e. if the user applies a different filter, the range would change.
    What you propose creates a snapshot: the name refers to a fixed range of cells, it doesn't matter whether they become visible again.
    But perhaps I'm mistaken.

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visible cells named ranges (Excel2000)

    Of course - you're probably right.

Posting Permissions

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