Drop Down List complication

[DoubleZ]

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?

 

[Gary''s Student]

For example to combine row #1 and rows #2 into row #3, in A3 enter:

=A1 & A2

and copy across.

 

[DoubleZ]

I think you meant to reply to the post just beneath mine.

 

[Gary''s Student]

Sorry:

For you application, make the dropdown list from a helper column with the
blanks removed. See:

http://www.cpearson.com/excel/noblanks.htm

 

[John C]

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.

 

[DoubleZ]

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]

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!