Home |
Search |
Today's Posts |
#1
|
|||
|
|||
named range with filter
hi,
Can you create a named range (using offset) and include a filter in this named range? thanks |
#2
|
|||
|
|||
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 __________________________ |
#3
|
|||
|
|||
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 __________________________ |
#4
|
|||
|
|||
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup in named range | Excel Discussion (Misc queries) | |||
Excel will not include all of my non-adj ranges in a named range?? | Excel Worksheet Functions | |||
Dynamic named range across multiple sheets | Excel Discussion (Misc queries) | |||
Advanced filter and Criteria Range | Excel Discussion (Misc queries) | |||
Count formula within a named range. | Excel Discussion (Misc queries) |