| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Ignore blank cells in a named range when using dependent data validation
I am using dependant data validation in cell ‘Sheet2!$B2’. It’s formula is ‘=INDIRECT($A2)’.
The dropdown menu in ‘Sheet2!$B2’ then references a named range in ‘Sheet1!$L:$L’, which at times has one or more blank cells at the top of the column. I would like to ignore the blank cells in the column so that only the populated cells in the named range appear in the drop down menu. I acquired the following formula from the net to replace ‘Sheet1!$L:$L’ in the name manager, but I am unable to make it work. =OFFSET(Sheet1!$L$2,0,0,MATCH("*",Sheet1!$L$2:$L$1 00,-1),1) Any help would be greatly appreciated. I'm using Excel 2007, Vista Last edited by JackyJ : September 15th 10 at 11:39 AM |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Validation - Ignore Blank | Excel Discussion (Misc queries) | |||
| Ignore blank checkbox-Data Validation | Excel Discussion (Misc queries) | |||
| What is the point of Data-Validation-Ignore Blank? | Excel Discussion (Misc queries) | |||
| Blank cells in named range- how to ignore them when making my graph? Help plz! | Excel Discussion (Misc queries) | |||
| How do I set up a drop down validation to ignore any blank cells? | Excel Discussion (Misc queries) | |||
Threaded Mode
