Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limiting the range of AutoFilter?
All, Thanks in advance for taking a look at my problem. I appreciate it. I'm trying to figure out how I limit the range that Autofilter searches. Basically, I have approximately 1000 rows of data, then a blank row, then a bunch more data beneath. I need to make it, so that when I click on the arrow, and autofilter for 'top 10', it only gives me the top 10 results in the upper range of data (the 1000 top rows), and not the data that is beyond those first 1000 rows. Is this possible using autofilter? I know I could do with the advanced filter, but this workbook is used by people who need it to be as simple as possible. Thanks for your help. C -- CDiddy ------------------------------------------------------------------------ CDiddy's Profile: http://www.excelforum.com/member.php...o&userid=29492 View this thread: http://www.excelforum.com/showthread...hreadid=539366 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limiting the range of AutoFilter?
Instead of selecting the entire columns when setting up the AutoFilter, just
select the cells that you want to be affected. So, highlight from Row 1 down through Row 1000. HTH, Elkar "CDiddy" wrote: All, Thanks in advance for taking a look at my problem. I appreciate it. I'm trying to figure out how I limit the range that Autofilter searches. Basically, I have approximately 1000 rows of data, then a blank row, then a bunch more data beneath. I need to make it, so that when I click on the arrow, and autofilter for 'top 10', it only gives me the top 10 results in the upper range of data (the 1000 top rows), and not the data that is beyond those first 1000 rows. Is this possible using autofilter? I know I could do with the advanced filter, but this workbook is used by people who need it to be as simple as possible. Thanks for your help. C -- CDiddy ------------------------------------------------------------------------ CDiddy's Profile: http://www.excelforum.com/member.php...o&userid=29492 View this thread: http://www.excelforum.com/showthread...hreadid=539366 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limiting the range of AutoFilter?
Hi Elkar, I have tried that, but it still pulls from the entire column. Any other thoughts? Thanks for the idea. -- CDiddy ------------------------------------------------------------------------ CDiddy's Profile: http://www.excelforum.com/member.php...o&userid=29492 View this thread: http://www.excelforum.com/showthread...hreadid=539366 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limiting the range of AutoFilter?
Hi
Add a column into your table, where a group identificator is calculated. P.e., when in column A always exist a value, when the row is not empty, then into some column (X) ento row2 enter the formula : =IF(A2="","",INT(ROW()/1000)+1) , or =IF(A2="","",LEFT(UPPER(A2)) Now you at first set autofilter to group column (the second formula allows to group records alfabetically), and then filter the result for 'top 10'. And avoid empty rows - otherwise you have to define the autofilter range manually every time. Arvi Laanemets "CDiddy" wrote in message ... All, Thanks in advance for taking a look at my problem. I appreciate it. I'm trying to figure out how I limit the range that Autofilter searches. Basically, I have approximately 1000 rows of data, then a blank row, then a bunch more data beneath. I need to make it, so that when I click on the arrow, and autofilter for 'top 10', it only gives me the top 10 results in the upper range of data (the 1000 top rows), and not the data that is beyond those first 1000 rows. Is this possible using autofilter? I know I could do with the advanced filter, but this workbook is used by people who need it to be as simple as possible. Thanks for your help. C -- CDiddy ------------------------------------------------------------------------ CDiddy's Profile: http://www.excelforum.com/member.php...o&userid=29492 View this thread: http://www.excelforum.com/showthread...hreadid=539366 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limiting the range of AutoFilter?
Hmm... that's strange, I tested it and it works fine for me. Are you sure
that row 1001 is completely blank? Maybe try turning off AutoFilter, then save your spreadsheet, then close. Open again and reapply AutoFilter. What version of Excel are you using? Excel 2003 is the only one I have access to at the moment, so perhaps AutoFilter behaves differently on older versions? Other than that, I'm not sure why it isn't working for you. "CDiddy" wrote: Hi Elkar, I have tried that, but it still pulls from the entire column. Any other thoughts? Thanks for the idea. -- CDiddy ------------------------------------------------------------------------ CDiddy's Profile: http://www.excelforum.com/member.php...o&userid=29492 View this thread: http://www.excelforum.com/showthread...hreadid=539366 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limiting the range of AutoFilter?
I've had similar problem limiting the rows to filter.
I found by chance this workaround: Name the rows or range of cells to filter (insert, name, cells, range) Select the range that you just created in the range name box to left of formula bar ( I also clicked/highlighted the range name but not sure if this is significant) now create auto filter Save the worksheet Now use autofilter dropdown list I also found that it takes a few blank rows after the range list before the next non- blank line. This was due to my using a lot of blank cells and lines in the range I think. Hope this helps........... SWURST "CDiddy" wrote: All, Thanks in advance for taking a look at my problem. I appreciate it. I'm trying to figure out how I limit the range that Autofilter searches. Basically, I have approximately 1000 rows of data, then a blank row, then a bunch more data beneath. I need to make it, so that when I click on the arrow, and autofilter for 'top 10', it only gives me the top 10 results in the upper range of data (the 1000 top rows), and not the data that is beyond those first 1000 rows. Is this possible using autofilter? I know I could do with the advanced filter, but this workbook is used by people who need it to be as simple as possible. Thanks for your help. C -- CDiddy ------------------------------------------------------------------------ CDiddy's Profile: http://www.excelforum.com/member.php...o&userid=29492 View this thread: http://www.excelforum.com/showthread...hreadid=539366 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limiting the range of a lookup function | Excel Worksheet Functions | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting empty cells within a range of cells | New Users to Excel |