Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List complication
I have a drop down list in EXCEL 2007 that includes the values from a range
that contains 8000 cells. Each one of these cells contains a formula. This worksheet is a template that could at times use all 8000 cells, but usually many of them will be blank. The problem is, since they include functions, they aren't actually empty, they just output "". This makes the dropdown list very long which makes it difficult to find values in it. How do I fix this problem? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List complication
For example to combine row #1 and rows #2 into row #3, in A3 enter:
=A1 & A2 and copy across. -- Gary''s Student - gsnu200794 "DoubleZ" wrote: I have a drop down list in EXCEL 2007 that includes the values from a range that contains 8000 cells. Each one of these cells contains a formula. This worksheet is a template that could at times use all 8000 cells, but usually many of them will be blank. The problem is, since they include functions, they aren't actually empty, they just output "". This makes the dropdown list very long which makes it difficult to find values in it. How do I fix this problem? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List complication
I think you meant to reply to the post just beneath mine.
"Gary''s Student" wrote: For example to combine row #1 and rows #2 into row #3, in A3 enter: =A1 & A2 and copy across. -- Gary''s Student - gsnu200794 "DoubleZ" wrote: I have a drop down list in EXCEL 2007 that includes the values from a range that contains 8000 cells. Each one of these cells contains a formula. This worksheet is a template that could at times use all 8000 cells, but usually many of them will be blank. The problem is, since they include functions, they aren't actually empty, they just output "". This makes the dropdown list very long which makes it difficult to find values in it. How do I fix this problem? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List complication
Sorry:
For you application, make the dropdown list from a helper column with the blanks removed. See: http://www.cpearson.com/excel/noblanks.htm -- Gary''s Student - gsnu200794 "DoubleZ" wrote: I think you meant to reply to the post just beneath mine. "Gary''s Student" wrote: For example to combine row #1 and rows #2 into row #3, in A3 enter: =A1 & A2 and copy across. -- Gary''s Student - gsnu200794 "DoubleZ" wrote: I have a drop down list in EXCEL 2007 that includes the values from a range that contains 8000 cells. Each one of these cells contains a formula. This worksheet is a template that could at times use all 8000 cells, but usually many of them will be blank. The problem is, since they include functions, they aren't actually empty, they just output "". This makes the dropdown list very long which makes it difficult to find values in it. How do I fix this problem? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List complication
Ugly solution alert, ugly solution alert. And not sure if you would want to
go this route. This is a NON-VBA route. Need of THREE helper columns. Assuming Sheet1 is the name of your primary sheet. Step 1: Insert a column in front of your column that will either be blank or contain data depending on the formula. (In my sample, I have inserted into column A). Step 2: If your data starts in row 1, type the following formula: =IF(C1="","",IF(ROW()=1,1,MAX(INDIRECT("A$"&ROW($C $2)-1&":A"&ROW()-1))+1)) Note: I am assuming the data that may be blank is in column C. If it starts in row 2, it can be much simpler: =IF(C2="","",MAX(A$1:A1)+1) Step 3: Preferably on a separate tab. Type the following in cell A1 (in my example, this is on Sheet 4). =IF(ROW()<=MAX(Sheet1!A:A),VLOOKUP(ROW(),Sheet1!$A $1:$C$8000,3,FALSE),"") Then copy down for the necessary 8000 rows (Like I said, ugly). Step 4: Define your List as follows, go to Insert--Name--Define: My example, I named the range as TheRange, and in Refers to: I entered: =OFFSET(Sheet4!$A$1,0,0,MAX(Sheet1!$A:$A),1) Step 5: Use your Data Validation, List, with the Source being =TheRange. It's ugly, but it works. I didn't test it over 8000 rows of formulas, so it might be boggy. -- John C "DoubleZ" wrote: I think you meant to reply to the post just beneath mine. "Gary''s Student" wrote: For example to combine row #1 and rows #2 into row #3, in A3 enter: =A1 & A2 and copy across. -- Gary''s Student - gsnu200794 "DoubleZ" wrote: I have a drop down list in EXCEL 2007 that includes the values from a range that contains 8000 cells. Each one of these cells contains a formula. This worksheet is a template that could at times use all 8000 cells, but usually many of them will be blank. The problem is, since they include functions, they aren't actually empty, they just output "". This makes the dropdown list very long which makes it difficult to find values in it. How do I fix this problem? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List complication
Thanks John C. That did the trick. Might I ask how to filter out any
duplicate values using something other than the 'remove duplicates' button? Is there a formula I can use? Thanks. "John C" wrote: Ugly solution alert, ugly solution alert. And not sure if you would want to go this route. This is a NON-VBA route. Need of THREE helper columns. Assuming Sheet1 is the name of your primary sheet. Step 1: Insert a column in front of your column that will either be blank or contain data depending on the formula. (In my sample, I have inserted into column A). Step 2: If your data starts in row 1, type the following formula: =IF(C1="","",IF(ROW()=1,1,MAX(INDIRECT("A$"&ROW($C $2)-1&":A"&ROW()-1))+1)) Note: I am assuming the data that may be blank is in column C. If it starts in row 2, it can be much simpler: =IF(C2="","",MAX(A$1:A1)+1) Step 3: Preferably on a separate tab. Type the following in cell A1 (in my example, this is on Sheet 4). =IF(ROW()<=MAX(Sheet1!A:A),VLOOKUP(ROW(),Sheet1!$A $1:$C$8000,3,FALSE),"") Then copy down for the necessary 8000 rows (Like I said, ugly). Step 4: Define your List as follows, go to Insert--Name--Define: My example, I named the range as TheRange, and in Refers to: I entered: =OFFSET(Sheet4!$A$1,0,0,MAX(Sheet1!$A:$A),1) Step 5: Use your Data Validation, List, with the Source being =TheRange. It's ugly, but it works. I didn't test it over 8000 rows of formulas, so it might be boggy. -- John C "DoubleZ" wrote: I think you meant to reply to the post just beneath mine. "Gary''s Student" wrote: For example to combine row #1 and rows #2 into row #3, in A3 enter: =A1 & A2 and copy across. -- Gary''s Student - gsnu200794 "DoubleZ" wrote: I have a drop down list in EXCEL 2007 that includes the values from a range that contains 8000 cells. Each one of these cells contains a formula. This worksheet is a template that could at times use all 8000 cells, but usually many of them will be blank. The problem is, since they include functions, they aren't actually empty, they just output "". This makes the dropdown list very long which makes it difficult to find values in it. How do I fix this problem? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List complication
You will modify the IF statement in step 2
=IF(C1="","",IF(COUNTIF(C$1:C1,C1)1,"",IF(ROW()=1 ,1,MAX(INDIRECT("A$"&ROW($C$2)-1&":A"&ROW()-1))+1))) or =IF(C2="","",IF(COUNTIF(C$1:C2,C2)1,"",MAX(A$1:A1 )+1)) Don't forget to check question being answered, and have a great day! -- John C "DoubleZ" wrote: Thanks John C. That did the trick. Might I ask how to filter out any duplicate values using something other than the 'remove duplicates' button? Is there a formula I can use? Thanks. "John C" wrote: Ugly solution alert, ugly solution alert. And not sure if you would want to go this route. This is a NON-VBA route. Need of THREE helper columns. Assuming Sheet1 is the name of your primary sheet. Step 1: Insert a column in front of your column that will either be blank or contain data depending on the formula. (In my sample, I have inserted into column A). Step 2: If your data starts in row 1, type the following formula: =IF(C1="","",IF(ROW()=1,1,MAX(INDIRECT("A$"&ROW($C $2)-1&":A"&ROW()-1))+1)) Note: I am assuming the data that may be blank is in column C. If it starts in row 2, it can be much simpler: =IF(C2="","",MAX(A$1:A1)+1) Step 3: Preferably on a separate tab. Type the following in cell A1 (in my example, this is on Sheet 4). =IF(ROW()<=MAX(Sheet1!A:A),VLOOKUP(ROW(),Sheet1!$A $1:$C$8000,3,FALSE),"") Then copy down for the necessary 8000 rows (Like I said, ugly). Step 4: Define your List as follows, go to Insert--Name--Define: My example, I named the range as TheRange, and in Refers to: I entered: =OFFSET(Sheet4!$A$1,0,0,MAX(Sheet1!$A:$A),1) Step 5: Use your Data Validation, List, with the Source being =TheRange. It's ugly, but it works. I didn't test it over 8000 rows of formulas, so it might be boggy. -- John C "DoubleZ" wrote: I think you meant to reply to the post just beneath mine. "Gary''s Student" wrote: For example to combine row #1 and rows #2 into row #3, in A3 enter: =A1 & A2 and copy across. -- Gary''s Student - gsnu200794 "DoubleZ" wrote: I have a drop down list in EXCEL 2007 that includes the values from a range that contains 8000 cells. Each one of these cells contains a formula. This worksheet is a template that could at times use all 8000 cells, but usually many of them will be blank. The problem is, since they include functions, they aren't actually empty, they just output "". This makes the dropdown list very long which makes it difficult to find values in it. How do I fix this problem? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down list dependant on previous drop down list | Excel Discussion (Misc queries) | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
Drop Down List choice selecting another drop down list | Excel Worksheet Functions | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
Drop dow list complication | Excel Discussion (Misc queries) |