Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Skip multiple blanks
The following details will skip blank cells in a range so that when creating
a custom validation list there will be no blanks. BA4:BA500 contain customer names BB4:BB500 contains the formula: =IF(INDIRECT("BA"&ROW(IV4))="","",ROW()) BC4:BC500 contains the formula: =INDEX(INDIRECT("$BA$4:$BA$500"),MATCH(SMALL($BB$4 :$BB$500,ROWS($IV$1:IV1)), $BB$4:$BB$500,0)) Defined range with the name SkipCustomerBlanks: =OFFSET(Customer!$BC$4,0,0,SUMPRODUCT(--NOT(ISERROR(Customer!$BC$4:$BC$500)) ),) When a customer name is deleted in BA the range BC4:BC500 then moves up one cell and the error #NUM! appears on the bottom cell. This does not cause a problem, the problem I am encountering is when a large number of customer names are deleted there comes a point where instead of the error #NUM! appears a zero (0) is returned instead. Where a zero is returned to a cell in BC4:BC500 the validation list will then display the zero (0) in the dropdown list. Anyone know what causes this to happen? Much appreciate if you can help. Pat |
#2
|
|||
|
|||
I have found what was causing the zeros to appear in the val list. Some
cells contained a formula which returned zero. I have removed these formulas. Pat "Pat" wrote in message ... The following details will skip blank cells in a range so that when creating a custom validation list there will be no blanks. BA4:BA500 contain customer names BB4:BB500 contains the formula: =IF(INDIRECT("BA"&ROW(IV4))="","",ROW()) BC4:BC500 contains the formula: =INDEX(INDIRECT("$BA$4:$BA$500"),MATCH(SMALL($BB$4 :$BB$500,ROWS($IV$1:IV1)), $BB$4:$BB$500,0)) Defined range with the name SkipCustomerBlanks: =OFFSET(Customer!$BC$4,0,0,SUMPRODUCT(--NOT(ISERROR(Customer!$BC$4:$BC$500)) ),) When a customer name is deleted in BA the range BC4:BC500 then moves up one cell and the error #NUM! appears on the bottom cell. This does not cause a problem, the problem I am encountering is when a large number of customer names are deleted there comes a point where instead of the error #NUM! appears a zero (0) is returned instead. Where a zero is returned to a cell in BC4:BC500 the validation list will then display the zero (0) in the dropdown list. Anyone know what causes this to happen? Much appreciate if you can help. Pat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding certain cells in multiple worksheets in multiple workbooks | Excel Worksheet Functions | |||
Return Multiple Results with Lookup | Excel Worksheet Functions | |||
XML / parent with multiple children and with multiple children | Excel Discussion (Misc queries) | |||
How do I avoid saving multiple Excel/Wordfiles for versioning purp | Excel Discussion (Misc queries) | |||
multiple entries | Excel Worksheet Functions |