named range with filter
 

hi,

Can you create a named range (using offset) and include a filter in this
named range?

thanks

named range with filter
 

On Mon, 17 Oct 2005 03:20:03 -0700, dee
wrote:

hi,

Can you create a named range (using offset) and include a filter in this
named range?

thanks

You can use either Insert Name Create or Insert Name Define.

With text "TEST1" in A1, highlight A1:D5 choose Insert Name Create and
select Top row and Left column. This sets the range name Test1 to
A2:D5 .


Insert Name Define, Name = "Test2", type the following in the Refers
to box:

=OFFSET(Sheet1!$A$10:$D$15,0,0):OFFSET(Sheet1!$A$1 0,5,5)

This sets the range name "Test2" to A10:F15

Better still just select the range you're interested in and type the
name you want, in the Name box immediately above the column A heading.

I can see no reason why filters won't work with these ranges.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

named range with filter
 

Hi,

thanks for your help, however, i understand how to create named ranges, but
i am wondering if it is possible to include a filter within the named range.
so for example, the named range will refer dynamically to the non zero rows
of a worksheet, where columnA = 'true'.
then when I copy and paste, the named range will only paste those non zero
rows which meet the filter criteria

thanks

"Richard Buttrey" wrote:

On Mon, 17 Oct 2005 03:20:03 -0700, dee
wrote:

hi,

Can you create a named range (using offset) and include a filter in this
named range?

thanks

You can use either Insert Name Create or Insert Name Define.

With text "TEST1" in A1, highlight A1:D5 choose Insert Name Create and
select Top row and Left column. This sets the range name Test1 to
A2:D5 .


Insert Name Define, Name = "Test2", type the following in the Refers
to box:

=OFFSET(Sheet1!$A$10:$D$15,0,0):OFFSET(Sheet1!$A$1 0,5,5)

This sets the range name "Test2" to A10:F15

Better still just select the range you're interested in and type the
name you want, in the Name box immediately above the column A heading.

I can see no reason why filters won't work with these ranges.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

named range with filter
 

If you copy and paste a filtered range, only the visible rows are pasted.

If you want non-zero rows, you need to include that criteria in your filter

--
Regards,
Tom Ogilvy


"confused" wrote in message
...
Hi,

thanks for your help, however, i understand how to create named ranges,
but
i am wondering if it is possible to include a filter within the named
range.
so for example, the named range will refer dynamically to the non zero
rows
of a worksheet, where columnA = 'true'.
then when I copy and paste, the named range will only paste those non zero
rows which meet the filter criteria

thanks

"Richard Buttrey" wrote:

On Mon, 17 Oct 2005 03:20:03 -0700, dee
wrote:

hi,

Can you create a named range (using offset) and include a filter in
this
named range?

thanks

You can use either Insert Name Create or Insert Name Define.

With text "TEST1" in A1, highlight A1:D5 choose Insert Name Create and
select Top row and Left column. This sets the range name Test1 to
A2:D5 .


Insert Name Define, Name = "Test2", type the following in the Refers
to box:

=OFFSET(Sheet1!$A$10:$D$15,0,0):OFFSET(Sheet1!$A$1 0,5,5)

This sets the range name "Test2" to A10:F15

Better still just select the range you're interested in and type the
name you want, in the Name box immediately above the column A heading.

I can see no reason why filters won't work with these ranges.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________