Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MS Excel Data Validation, Ignore Blanks in List
Hello,
I have a problem utilizing the Data Validation tool in Excel. If I have a list in one column with the following, for example: ----------------- Dog Cat Rabbit Frog Kangaroo ----------------- See how the list is really six items long? However, if I use a data validation on a separate cell with a List referring to the above range of animals, the validation list shows the exact same list: ----------------- Dog Cat Rabbit Frog Kangaroo ----------------- However, how could I fix this without using "Sort" so that the data validation list in the cell shows the following: ----------------- Dog Cat Rabbit Frog Kangaroo ----------------- Where the rows are only populated by nonblank values. This is especially handy when my real range is say hundreds of rows long with many blank cells inbetween the beginning and the end of the range. I've seen a lot of other forums where people have suggested using the Offset function; however, that only works to dynamically cut off the range when there are blanks following the nonblank cells. It does NOT work for ranges with intermittent blanks. Thanks, D |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MS Excel Data Validation, Ignore Blanks in List
One way to get there ..
Assume the source for the DV is in Sheet1's A1:A1000 In C1: =IF(A1="","",ROW()) In D1: =INDEX(A:A,SMALL(C:C,ROW())) Select C1:D1, copy down to D1000 Then you could use this expression as the dynamic range for your DV: =OFFSET(Sheet1!$D$1,,,SUMPRODUCT(--NOT(ISERROR(Sheet1!$D$1:$D$1000)))) where the height param is given by the sumproduct expression on the derived col D as the proxy source. The DV droplist will then return the exact results that you seek, ie expand or contract accordingly. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "D Lu" wrote in message ... Hello, I have a problem utilizing the Data Validation tool in Excel. If I have a list in one column with the following, for example: ----------------- Dog Cat Rabbit Frog Kangaroo ----------------- See how the list is really six items long? However, if I use a data validation on a separate cell with a List referring to the above range of animals, the validation list shows the exact same list: ----------------- Dog Cat Rabbit Frog Kangaroo ----------------- However, how could I fix this without using "Sort" so that the data validation list in the cell shows the following: ----------------- Dog Cat Rabbit Frog Kangaroo ----------------- Where the rows are only populated by nonblank values. This is especially handy when my real range is say hundreds of rows long with many blank cells inbetween the beginning and the end of the range. I've seen a lot of other forums where people have suggested using the Offset function; however, that only works to dynamically cut off the range when there are blanks following the nonblank cells. It does NOT work for ranges with intermittent blanks. Thanks, D |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MS Excel Data Validation, Ignore Blanks in List
Max,
I like your idea with the C1 and D1 and it works well. However, if I have a list of say, six elements like above but one element is blank, the DV list now shows the five non-blank elements and then a #NUM! error. If I have seven elements two of which are non-blank and non- consecutive, then the DV list displays the five non-blank and then two #NUM! errors. Anyway to remove those #NUM! errors? Thanks, D |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MS Excel Data Validation, Ignore Blanks in List
On Mar 20, 1:38 am, "Max" wrote:
One way to get there .. Assume the source for the DV is in Sheet1's A1:A1000 In C1: =IF(A1="","",ROW()) In D1: =INDEX(A:A,SMALL(C:C,ROW())) Select C1:D1, copy down to D1000 Then you could use this expression as the dynamic range for your DV: =OFFSET(Sheet1!$D$1,,,SUMPRODUCT(--NOT(ISERROR(Sheet1!$D$1:$D$1000)))) where the height param is given by the sumproduct expression on the derived col D as the proxy source. The DV droplist will then return the exact results that you seek, ie expand or contract accordingly. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik ---"D Lu" wrote in message ... Hello, I have a problem utilizing theDataValidationtool inExcel. If I have alistin one column with the following, for example: ----------------- Dog Cat Rabbit Frog Kangaroo ----------------- See how thelistis really six items long? However, if I use adata validationon a separate cell with aListreferring to the above range of animals, thevalidationlistshows the exact samelist: ----------------- Dog Cat Rabbit Frog Kangaroo ----------------- However, how could I fix this without using "Sort" so that thedata validationlistin the cell shows the following: ----------------- Dog Cat Rabbit Frog Kangaroo ----------------- Where the rows are only populated by nonblank values. This is especially handy when my real range is say hundreds of rows long with many blank cells inbetween the beginning and the end of the range. I've seen a lot of other forums where people have suggested using the Offset function; however, that only works to dynamically cut off the range when there areblanksfollowing the nonblank cells. It does NOT work for ranges with intermittentblanks. Thanks, D I just wrote to you... I found and solved the problem. Thanks a million!! Regards, D |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MS Excel Data Validation, Ignore Blanks in List
Welcome, glad to hear that.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "D Lu" wrote I just wrote to you... I found and solved the problem. Thanks a million!! Max, I like your idea with the C1 and D1 and it works well. However, if I have a list of say, six elements like above but one element is blank, the DV list now shows the five non-blank elements and then a #NUM! error. If I have seven elements two of which are non-blank and non- consecutive, then the DV list displays the five non-blank and then two #NUM! errors. Anyway to remove those #NUM! errors? Thanks, D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blanks Data Validation List | Excel Discussion (Misc queries) | |||
DATA Validation (Ignore Blanks) | Excel Discussion (Misc queries) | |||
Why is * valid if Excel data validation list has no blanks or *? | Excel Worksheet Functions | |||
Data Validation - ignore blanks | Excel Discussion (Misc queries) | |||
Ignore Blanks in Data Validation | Excel Worksheet Functions |