Visible cells named ranges (Excel2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Of course – you’re probably right.

Reply | Quote