Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dropdown list and filtered data
I am having major dramas trying to figure out how to get the information in
my dropdown list to show unly filtered data. I will try to explain what I am doing and hopefully someone can help me. By the way I have no idea how to do Macros or any VB stuff so Please if you have a solution an easy explanation would be appreciated. I have about 50 columns of data and about 4000 rows in sheet 2. This is a database auto refresh query I have created. The data in this sheet needs to show in sheet 2 as it is refreshed each time but only column A needs to be seen based on the criteria in column J having something in it (NONBLANK) so basically if there is nothing in column J I do not want the data to show up in the list on sheet 1. I have done a basic filter on sheet 2 but if I try to create a link on sheet 1 to column A so all the names show I am still getting all the filtered (BLANK) cells show up. I know I can just copy the information over once I have done the filter but this is not going to help me as I need it to auto refresh each time the cells change. Is there an easy way to filter from one sheet to another and have it so the criteria is to only show names from column A that are not blank in column J? I really hope waht I am asking makes sense. Tahnks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dropdown list and filtered data
Kerrie,
On your Sheet1, in cell A2, array enter the formula (enter using Ctrl-Shift-Enter) =IF(COUNTA('DataBase Extract'!$J$2:$J$5000)=ROW()-ROW($A$2)+1,INDIRECT("'DataBase Extract'!" & ADDRESS(LARGE(('DataBase Extract'!$J$2:$J$5000<"")*ROW('DataBase Extract'!$J$2:$J$5000),COUNTA('DataBase Extract'!$J$2:$J$5000)+ROW($A$2)-ROW()),COLUMN()-COLUMN($A$2)+1)),"") Then copy down for 4000 or so rows....If you could possilby have more than 5000 records extracted, then increase the 3 instances of 5000 to some other larger number. Note that this formula is written so that it can be copied into column B, C, etc, to extract those values as well from sheet1, in case you need more than just column A. HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... I am having major dramas trying to figure out how to get the information in my dropdown list to show unly filtered data. I will try to explain what I am doing and hopefully someone can help me. By the way I have no idea how to do Macros or any VB stuff so Please if you have a solution an easy explanation would be appreciated. I have about 50 columns of data and about 4000 rows in sheet 2. This is a database auto refresh query I have created. The data in this sheet needs to show in sheet 2 as it is refreshed each time but only column A needs to be seen based on the criteria in column J having something in it (NONBLANK) so basically if there is nothing in column J I do not want the data to show up in the list on sheet 1. I have done a basic filter on sheet 2 but if I try to create a link on sheet 1 to column A so all the names show I am still getting all the filtered (BLANK) cells show up. I know I can just copy the information over once I have done the filter but this is not going to help me as I need it to auto refresh each time the cells change. Is there an easy way to filter from one sheet to another and have it so the criteria is to only show names from column A that are not blank in column J? I really hope waht I am asking makes sense. Tahnks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dropdown list and filtered data
Bernie thank you for your help but I am getting an N/A error when I try the
formula in cell A2 On my sheet 1 I need the information to be placed into row BP as it is the column I have linked to my data validation. All the other rows have other data in them. I need the list of names from sheet 2 to show in column BP on sheet 1 based on the fact that if there is no data in column J on the sheet 2 then the information from column A in sheet 2 does not show up. Below is an example of how the data on sheet 2 is A J Figjam (Blank) People 10 Carpenter Pluto (Blank) Happy 12 Plumber OMG 15 Electrician I just want to get the list of names from column A (sheet2) to be sent through onto sheet1 based on the fact that column J actually has something in it and is not blank. I need to do this so my dropdown list can show only the names from sheet2 that have data in column J. This is driving me nuts!! I really want to do a course in Excel. I know it can be done I just dont know how to do it! it would be easier if I could figure out how to actually filter the data from ACT so that when I do the Import data into sheet 2 only the names I wanted came through but life was not meant to be easy.... Thanks for your help and I really hope I am not confusing you more. "Bernie Deitrick" wrote: Kerrie, On your Sheet1, in cell A2, array enter the formula (enter using Ctrl-Shift-Enter) =IF(COUNTA('DataBase Extract'!$J$2:$J$5000)=ROW()-ROW($A$2)+1,INDIRECT("'DataBase Extract'!" & ADDRESS(LARGE(('DataBase Extract'!$J$2:$J$5000<"")*ROW('DataBase Extract'!$J$2:$J$5000),COUNTA('DataBase Extract'!$J$2:$J$5000)+ROW($A$2)-ROW()),COLUMN()-COLUMN($A$2)+1)),"") Then copy down for 4000 or so rows....If you could possilby have more than 5000 records extracted, then increase the 3 instances of 5000 to some other larger number. Note that this formula is written so that it can be copied into column B, C, etc, to extract those values as well from sheet1, in case you need more than just column A. HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... I am having major dramas trying to figure out how to get the information in my dropdown list to show unly filtered data. I will try to explain what I am doing and hopefully someone can help me. By the way I have no idea how to do Macros or any VB stuff so Please if you have a solution an easy explanation would be appreciated. I have about 50 columns of data and about 4000 rows in sheet 2. This is a database auto refresh query I have created. The data in this sheet needs to show in sheet 2 as it is refreshed each time but only column A needs to be seen based on the criteria in column J having something in it (NONBLANK) so basically if there is nothing in column J I do not want the data to show up in the list on sheet 1. I have done a basic filter on sheet 2 but if I try to create a link on sheet 1 to column A so all the names show I am still getting all the filtered (BLANK) cells show up. I know I can just copy the information over once I have done the filter but this is not going to help me as I need it to auto refresh each time the cells change. Is there an easy way to filter from one sheet to another and have it so the criteria is to only show names from column A that are not blank in column J? I really hope waht I am asking makes sense. Tahnks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dropdown list and filtered data
Kerrie,
I cannot get it to give me an NA error... did you change the sheet name in this part: INDIRECT("'DataBase Extract'!" Since it is a string, it does not auto update. If you cannot get it to work, send me a copy of your file - take out the spaces and change the dot to . HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... Bernie thank you for your help but I am getting an N/A error when I try the formula in cell A2 On my sheet 1 I need the information to be placed into row BP as it is the column I have linked to my data validation. All the other rows have other data in them. I need the list of names from sheet 2 to show in column BP on sheet 1 based on the fact that if there is no data in column J on the sheet 2 then the information from column A in sheet 2 does not show up. Below is an example of how the data on sheet 2 is A J Figjam (Blank) People 10 Carpenter Pluto (Blank) Happy 12 Plumber OMG 15 Electrician I just want to get the list of names from column A (sheet2) to be sent through onto sheet1 based on the fact that column J actually has something in it and is not blank. I need to do this so my dropdown list can show only the names from sheet2 that have data in column J. This is driving me nuts!! I really want to do a course in Excel. I know it can be done I just dont know how to do it! it would be easier if I could figure out how to actually filter the data from ACT so that when I do the Import data into sheet 2 only the names I wanted came through but life was not meant to be easy.... Thanks for your help and I really hope I am not confusing you more. "Bernie Deitrick" wrote: Kerrie, On your Sheet1, in cell A2, array enter the formula (enter using Ctrl-Shift-Enter) =IF(COUNTA('DataBase Extract'!$J$2:$J$5000)=ROW()-ROW($A$2)+1,INDIRECT("'DataBase Extract'!" & ADDRESS(LARGE(('DataBase Extract'!$J$2:$J$5000<"")*ROW('DataBase Extract'!$J$2:$J$5000),COUNTA('DataBase Extract'!$J$2:$J$5000)+ROW($A$2)-ROW()),COLUMN()-COLUMN($A$2)+1)),"") Then copy down for 4000 or so rows....If you could possilby have more than 5000 records extracted, then increase the 3 instances of 5000 to some other larger number. Note that this formula is written so that it can be copied into column B, C, etc, to extract those values as well from sheet1, in case you need more than just column A. HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... I am having major dramas trying to figure out how to get the information in my dropdown list to show unly filtered data. I will try to explain what I am doing and hopefully someone can help me. By the way I have no idea how to do Macros or any VB stuff so Please if you have a solution an easy explanation would be appreciated. I have about 50 columns of data and about 4000 rows in sheet 2. This is a database auto refresh query I have created. The data in this sheet needs to show in sheet 2 as it is refreshed each time but only column A needs to be seen based on the criteria in column J having something in it (NONBLANK) so basically if there is nothing in column J I do not want the data to show up in the list on sheet 1. I have done a basic filter on sheet 2 but if I try to create a link on sheet 1 to column A so all the names show I am still getting all the filtered (BLANK) cells show up. I know I can just copy the information over once I have done the filter but this is not going to help me as I need it to auto refresh each time the cells change. Is there an easy way to filter from one sheet to another and have it so the criteria is to only show names from column A that are not blank in column J? I really hope waht I am asking makes sense. Tahnks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dropdown list and filtered data
Bernie. No sorry I had not changed the sheet name. I have changed it now and
I am getting the error #NUM! If it helps my Sheets are Sheet1 (Where I need the list in column BP) Sheet2 where the data extraction is. "Bernie Deitrick" wrote: Kerrie, I cannot get it to give me an NA error... did you change the sheet name in this part: INDIRECT("'DataBase Extract'!" Since it is a string, it does not auto update. If you cannot get it to work, send me a copy of your file - take out the spaces and change the dot to . HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... Bernie thank you for your help but I am getting an N/A error when I try the formula in cell A2 On my sheet 1 I need the information to be placed into row BP as it is the column I have linked to my data validation. All the other rows have other data in them. I need the list of names from sheet 2 to show in column BP on sheet 1 based on the fact that if there is no data in column J on the sheet 2 then the information from column A in sheet 2 does not show up. Below is an example of how the data on sheet 2 is A J Figjam (Blank) People 10 Carpenter Pluto (Blank) Happy 12 Plumber OMG 15 Electrician I just want to get the list of names from column A (sheet2) to be sent through onto sheet1 based on the fact that column J actually has something in it and is not blank. I need to do this so my dropdown list can show only the names from sheet2 that have data in column J. This is driving me nuts!! I really want to do a course in Excel. I know it can be done I just dont know how to do it! it would be easier if I could figure out how to actually filter the data from ACT so that when I do the Import data into sheet 2 only the names I wanted came through but life was not meant to be easy.... Thanks for your help and I really hope I am not confusing you more. "Bernie Deitrick" wrote: Kerrie, On your Sheet1, in cell A2, array enter the formula (enter using Ctrl-Shift-Enter) =IF(COUNTA('DataBase Extract'!$J$2:$J$5000)=ROW()-ROW($A$2)+1,INDIRECT("'DataBase Extract'!" & ADDRESS(LARGE(('DataBase Extract'!$J$2:$J$5000<"")*ROW('DataBase Extract'!$J$2:$J$5000),COUNTA('DataBase Extract'!$J$2:$J$5000)+ROW($A$2)-ROW()),COLUMN()-COLUMN($A$2)+1)),"") Then copy down for 4000 or so rows....If you could possilby have more than 5000 records extracted, then increase the 3 instances of 5000 to some other larger number. Note that this formula is written so that it can be copied into column B, C, etc, to extract those values as well from sheet1, in case you need more than just column A. HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... I am having major dramas trying to figure out how to get the information in my dropdown list to show unly filtered data. I will try to explain what I am doing and hopefully someone can help me. By the way I have no idea how to do Macros or any VB stuff so Please if you have a solution an easy explanation would be appreciated. I have about 50 columns of data and about 4000 rows in sheet 2. This is a database auto refresh query I have created. The data in this sheet needs to show in sheet 2 as it is refreshed each time but only column A needs to be seen based on the criteria in column J having something in it (NONBLANK) so basically if there is nothing in column J I do not want the data to show up in the list on sheet 1. I have done a basic filter on sheet 2 but if I try to create a link on sheet 1 to column A so all the names show I am still getting all the filtered (BLANK) cells show up. I know I can just copy the information over once I have done the filter but this is not going to help me as I need it to auto refresh each time the cells change. Is there an easy way to filter from one sheet to another and have it so the criteria is to only show names from column A that are not blank in column J? I really hope waht I am asking makes sense. Tahnks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dropdown list and filtered data
Kerrie,
In BP2, enter this array formula - You MUST use Ctrl-Shift-Enter and not just Enter to make this formula work =IF(COUNTA(Sheet2!$J$2:$J$5000)=ROW()-ROW($A$2)+1,INDIRECT("Sheet2!" & ADDRESS(LARGE((Sheet2!$J$2:$J$5000<"")*ROW(Sheet2 !$J$2:$J$5000),COUNTA(Sheet2!$J$2:$J$5000)+ROW($A$ 2)-ROW()),COLUMN()-COLUMN($BP$2)+1)),"") HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... Bernie. No sorry I had not changed the sheet name. I have changed it now and I am getting the error #NUM! If it helps my Sheets are Sheet1 (Where I need the list in column BP) Sheet2 where the data extraction is. "Bernie Deitrick" wrote: Kerrie, I cannot get it to give me an NA error... did you change the sheet name in this part: INDIRECT("'DataBase Extract'!" Since it is a string, it does not auto update. If you cannot get it to work, send me a copy of your file - take out the spaces and change the dot to . HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... Bernie thank you for your help but I am getting an N/A error when I try the formula in cell A2 On my sheet 1 I need the information to be placed into row BP as it is the column I have linked to my data validation. All the other rows have other data in them. I need the list of names from sheet 2 to show in column BP on sheet 1 based on the fact that if there is no data in column J on the sheet 2 then the information from column A in sheet 2 does not show up. Below is an example of how the data on sheet 2 is A J Figjam (Blank) People 10 Carpenter Pluto (Blank) Happy 12 Plumber OMG 15 Electrician I just want to get the list of names from column A (sheet2) to be sent through onto sheet1 based on the fact that column J actually has something in it and is not blank. I need to do this so my dropdown list can show only the names from sheet2 that have data in column J. This is driving me nuts!! I really want to do a course in Excel. I know it can be done I just dont know how to do it! it would be easier if I could figure out how to actually filter the data from ACT so that when I do the Import data into sheet 2 only the names I wanted came through but life was not meant to be easy.... Thanks for your help and I really hope I am not confusing you more. "Bernie Deitrick" wrote: Kerrie, On your Sheet1, in cell A2, array enter the formula (enter using Ctrl-Shift-Enter) =IF(COUNTA('DataBase Extract'!$J$2:$J$5000)=ROW()-ROW($A$2)+1,INDIRECT("'DataBase Extract'!" & ADDRESS(LARGE(('DataBase Extract'!$J$2:$J$5000<"")*ROW('DataBase Extract'!$J$2:$J$5000),COUNTA('DataBase Extract'!$J$2:$J$5000)+ROW($A$2)-ROW()),COLUMN()-COLUMN($A$2)+1)),"") Then copy down for 4000 or so rows....If you could possilby have more than 5000 records extracted, then increase the 3 instances of 5000 to some other larger number. Note that this formula is written so that it can be copied into column B, C, etc, to extract those values as well from sheet1, in case you need more than just column A. HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... I am having major dramas trying to figure out how to get the information in my dropdown list to show unly filtered data. I will try to explain what I am doing and hopefully someone can help me. By the way I have no idea how to do Macros or any VB stuff so Please if you have a solution an easy explanation would be appreciated. I have about 50 columns of data and about 4000 rows in sheet 2. This is a database auto refresh query I have created. The data in this sheet needs to show in sheet 2 as it is refreshed each time but only column A needs to be seen based on the criteria in column J having something in it (NONBLANK) so basically if there is nothing in column J I do not want the data to show up in the list on sheet 1. I have done a basic filter on sheet 2 but if I try to create a link on sheet 1 to column A so all the names show I am still getting all the filtered (BLANK) cells show up. I know I can just copy the information over once I have done the filter but this is not going to help me as I need it to auto refresh each time the cells change. Is there an easy way to filter from one sheet to another and have it so the criteria is to only show names from column A that are not blank in column J? I really hope waht I am asking makes sense. Tahnks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dropdown list and filtered data
You are a LEGEND!!!! Thank you soooooooo much... It Worked!!!...
YAY...*happy dance* "Bernie Deitrick" wrote: Kerrie, In BP2, enter this array formula - You MUST use Ctrl-Shift-Enter and not just Enter to make this formula work =IF(COUNTA(Sheet2!$J$2:$J$5000)=ROW()-ROW($A$2)+1,INDIRECT("Sheet2!" & ADDRESS(LARGE((Sheet2!$J$2:$J$5000<"")*ROW(Sheet2 !$J$2:$J$5000),COUNTA(Sheet2!$J$2:$J$5000)+ROW($A$ 2)-ROW()),COLUMN()-COLUMN($BP$2)+1)),"") HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... Bernie. No sorry I had not changed the sheet name. I have changed it now and I am getting the error #NUM! If it helps my Sheets are Sheet1 (Where I need the list in column BP) Sheet2 where the data extraction is. "Bernie Deitrick" wrote: Kerrie, I cannot get it to give me an NA error... did you change the sheet name in this part: INDIRECT("'DataBase Extract'!" Since it is a string, it does not auto update. If you cannot get it to work, send me a copy of your file - take out the spaces and change the dot to . HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... Bernie thank you for your help but I am getting an N/A error when I try the formula in cell A2 On my sheet 1 I need the information to be placed into row BP as it is the column I have linked to my data validation. All the other rows have other data in them. I need the list of names from sheet 2 to show in column BP on sheet 1 based on the fact that if there is no data in column J on the sheet 2 then the information from column A in sheet 2 does not show up. Below is an example of how the data on sheet 2 is A J Figjam (Blank) People 10 Carpenter Pluto (Blank) Happy 12 Plumber OMG 15 Electrician I just want to get the list of names from column A (sheet2) to be sent through onto sheet1 based on the fact that column J actually has something in it and is not blank. I need to do this so my dropdown list can show only the names from sheet2 that have data in column J. This is driving me nuts!! I really want to do a course in Excel. I know it can be done I just dont know how to do it! it would be easier if I could figure out how to actually filter the data from ACT so that when I do the Import data into sheet 2 only the names I wanted came through but life was not meant to be easy.... Thanks for your help and I really hope I am not confusing you more. "Bernie Deitrick" wrote: Kerrie, On your Sheet1, in cell A2, array enter the formula (enter using Ctrl-Shift-Enter) =IF(COUNTA('DataBase Extract'!$J$2:$J$5000)=ROW()-ROW($A$2)+1,INDIRECT("'DataBase Extract'!" & ADDRESS(LARGE(('DataBase Extract'!$J$2:$J$5000<"")*ROW('DataBase Extract'!$J$2:$J$5000),COUNTA('DataBase Extract'!$J$2:$J$5000)+ROW($A$2)-ROW()),COLUMN()-COLUMN($A$2)+1)),"") Then copy down for 4000 or so rows....If you could possilby have more than 5000 records extracted, then increase the 3 instances of 5000 to some other larger number. Note that this formula is written so that it can be copied into column B, C, etc, to extract those values as well from sheet1, in case you need more than just column A. HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... I am having major dramas trying to figure out how to get the information in my dropdown list to show unly filtered data. I will try to explain what I am doing and hopefully someone can help me. By the way I have no idea how to do Macros or any VB stuff so Please if you have a solution an easy explanation would be appreciated. I have about 50 columns of data and about 4000 rows in sheet 2. This is a database auto refresh query I have created. The data in this sheet needs to show in sheet 2 as it is refreshed each time but only column A needs to be seen based on the criteria in column J having something in it (NONBLANK) so basically if there is nothing in column J I do not want the data to show up in the list on sheet 1. I have done a basic filter on sheet 2 but if I try to create a link on sheet 1 to column A so all the names show I am still getting all the filtered (BLANK) cells show up. I know I can just copy the information over once I have done the filter but this is not going to help me as I need it to auto refresh each time the cells change. Is there an easy way to filter from one sheet to another and have it so the criteria is to only show names from column A that are not blank in column J? I really hope waht I am asking makes sense. Tahnks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dropdown list and filtered data
You are a LEGEND!!!! Yes, a legend in my own mind ;- Thank you soooooooo much... It Worked!!!... You're quite welcome, and thanks for letting me know that you got it to work. Bernie |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dropdown list and filtered data
Bernie,
I just made a new post but thought if there's any chance you are still subscribing to this post perhaps you'd see this first. I have the same exact situation as reflected below, but I cannot get your array formula to work for me. Instead of Sheet2!A2:J5000, my range is Termd!A4:E103 (person in A, status in E). Instead of "non-blanks", I need all status of "Involuntary" (or all status of <"Voluntary", whichever is easier to use) Instead of Sheet1!BP2 as the destination, mine is Plans!Q4. Here's what I tried to no avail. Help? =IF(COUNTA(Termd!$A$4:$E$103)=ROW()-ROW($A$4)+1, INDIRECT("Termd!" & ADDRESS(LARGE((Termd!$E$4:$E$103<"Voluntary")*ROW (Termd!$E$4:$E$103), COUNTA(Termd!$E$4:$E$103)+ROW($A$4)-ROW()),COLUMN()-COLUMN($Q$4)+1)),"") -- Please remember to indicate when the post is answered so others can benefit from it later. "Bernie Deitrick" wrote: Kerrie, In BP2, enter this array formula - You MUST use Ctrl-Shift-Enter and not just Enter to make this formula work =IF(COUNTA(Sheet2!$J$2:$J$5000)=ROW()-ROW($A$2)+1,INDIRECT("Sheet2!" & ADDRESS(LARGE((Sheet2!$J$2:$J$5000<"")*ROW(Sheet2 !$J$2:$J$5000),COUNTA(Sheet2!$J$2:$J$5000)+ROW($A$ 2)-ROW()),COLUMN()-COLUMN($BP$2)+1)),"") HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... Bernie. No sorry I had not changed the sheet name. I have changed it now and I am getting the error #NUM! If it helps my Sheets are Sheet1 (Where I need the list in column BP) Sheet2 where the data extraction is. "Bernie Deitrick" wrote: Kerrie, I cannot get it to give me an NA error... did you change the sheet name in this part: INDIRECT("'DataBase Extract'!" Since it is a string, it does not auto update. If you cannot get it to work, send me a copy of your file - take out the spaces and change the dot to . HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... Bernie thank you for your help but I am getting an N/A error when I try the formula in cell A2 On my sheet 1 I need the information to be placed into row BP as it is the column I have linked to my data validation. All the other rows have other data in them. I need the list of names from sheet 2 to show in column BP on sheet 1 based on the fact that if there is no data in column J on the sheet 2 then the information from column A in sheet 2 does not show up. Below is an example of how the data on sheet 2 is A J Figjam (Blank) People 10 Carpenter Pluto (Blank) Happy 12 Plumber OMG 15 Electrician I just want to get the list of names from column A (sheet2) to be sent through onto sheet1 based on the fact that column J actually has something in it and is not blank. I need to do this so my dropdown list can show only the names from sheet2 that have data in column J. This is driving me nuts!! I really want to do a course in Excel. I know it can be done I just dont know how to do it! it would be easier if I could figure out how to actually filter the data from ACT so that when I do the Import data into sheet 2 only the names I wanted came through but life was not meant to be easy.... Thanks for your help and I really hope I am not confusing you more. "Bernie Deitrick" wrote: Kerrie, On your Sheet1, in cell A2, array enter the formula (enter using Ctrl-Shift-Enter) =IF(COUNTA('DataBase Extract'!$J$2:$J$5000)=ROW()-ROW($A$2)+1,INDIRECT("'DataBase Extract'!" & ADDRESS(LARGE(('DataBase Extract'!$J$2:$J$5000<"")*ROW('DataBase Extract'!$J$2:$J$5000),COUNTA('DataBase Extract'!$J$2:$J$5000)+ROW($A$2)-ROW()),COLUMN()-COLUMN($A$2)+1)),"") Then copy down for 4000 or so rows....If you could possilby have more than 5000 records extracted, then increase the 3 instances of 5000 to some other larger number. Note that this formula is written so that it can be copied into column B, C, etc, to extract those values as well from sheet1, in case you need more than just column A. HTH, Bernie MS Excel MVP "Kerrie Wood 45693" wrote in message ... I am having major dramas trying to figure out how to get the information in my dropdown list to show unly filtered data. I will try to explain what I am doing and hopefully someone can help me. By the way I have no idea how to do Macros or any VB stuff so Please if you have a solution an easy explanation would be appreciated. I have about 50 columns of data and about 4000 rows in sheet 2. This is a database auto refresh query I have created. The data in this sheet needs to show in sheet 2 as it is refreshed each time but only column A needs to be seen based on the criteria in column J having something in it (NONBLANK) so basically if there is nothing in column J I do not want the data to show up in the list on sheet 1. I have done a basic filter on sheet 2 but if I try to create a link on sheet 1 to column A so all the names show I am still getting all the filtered (BLANK) cells show up. I know I can just copy the information over once I have done the filter but this is not going to help me as I need it to auto refresh each time the cells change. Is there an easy way to filter from one sheet to another and have it so the criteria is to only show names from column A that are not blank in column J? I really hope waht I am asking makes sense. Tahnks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting data from a filtered list | Excel Discussion (Misc queries) | |||
Grabbing the filtered data in a list? | Excel Worksheet Functions | |||
Grabbing the filtered data in a list? | Excel Worksheet Functions | |||
Select data in filtered list | Excel Discussion (Misc queries) | |||
Drop down list from filtered data | Excel Discussion (Misc queries) |