Grabbing the filtered data in a list?
I have a table that I have turned into a list. I narrow down the table
by choosing what to show in the header drop downs until I just have one row of data showing. How can I grab the numbers that are showing after I have chosen the selected data in each column? The list seems to hide the non-matching rows but I can't figure out how to reference just the remaining row of data. Any ideas? |
Grabbing the filtered data in a list?
Try Edit/GoTo/Special/ Visible Cells only maybe you will be able to copy only
what you want. -- David " wrote: I have a table that I have turned into a list. I narrow down the table by choosing what to show in the header drop downs until I just have one row of data showing. How can I grab the numbers that are showing after I have chosen the selected data in each column? The list seems to hide the non-matching rows but I can't figure out how to reference just the remaining row of data. Any ideas? |
Grabbing the filtered data in a list?
Say the list of data is in C2:C7 w/the header in cell C1. This should return
the first visible item in the dataset (note this formula must be confirmed w/control+shift+enter as it is an array formula): =INDEX(C2:C7,MATCH(1,SUBTOTAL(3,OFFSET(C2,ROW(C2:C 7)-ROW(C2),0,1,1)),0)) Is this what you're after? " wrote: I have a table that I have turned into a list. I narrow down the table by choosing what to show in the header drop downs until I just have one row of data showing. How can I grab the numbers that are showing after I have chosen the selected data in each column? The list seems to hide the non-matching rows but I can't figure out how to reference just the remaining row of data. Any ideas? |
Grabbing the filtered data in a list?
BTW - the formula is not my idea. I saw Bob Phillips use it (not sure if he
came up w/it, either). Someone else is due the credit, I'm just not positive who to give it to. "JMB" wrote: Say the list of data is in C2:C7 w/the header in cell C1. This should return the first visible item in the dataset (note this formula must be confirmed w/control+shift+enter as it is an array formula): =INDEX(C2:C7,MATCH(1,SUBTOTAL(3,OFFSET(C2,ROW(C2:C 7)-ROW(C2),0,1,1)),0)) Is this what you're after? " wrote: I have a table that I have turned into a list. I narrow down the table by choosing what to show in the header drop downs until I just have one row of data showing. How can I grab the numbers that are showing after I have chosen the selected data in each column? The list seems to hide the non-matching rows but I can't figure out how to reference just the remaining row of data. Any ideas? |
All times are GMT +1. The time now is 06:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com