Remove blanks with formulas in from my drop down menu

[Peter Davison]

Hi I have a list of cells in my drop down menu on one sheet. (My drop down menu selection box is on a separate sheet) All cells have a formula in which populates some cells with a result and leaves some blank. My drop down menu has all the cells showing even the blanks. Is there a way I can remove blanks from the drop down list?
I've tried offset but it thinks the blank cells with formulas in are a non blank cell.
Any help would be great.

 

[mamady]

Hi Dear,

Try the following, please:

1. select the range/column where you create your drop list from
2. give the selected range a name by simply typing in the Name Box (e.g. =Products)
3. from the Formulas ribbon, select Name Manager
4. Edit the created range, and type-in the following formula: =OFFSET(XXX!$B$2,0,0,COUNTA(XXX!$B:$B)-1,1) [replace XXX by the sheet name, and B by the range that has blanks]
5. Go to Data ribbon, selection Data Validation, select List and in the formula section just refer to the created name (e.g. =Products)

Best Regards
M. Yusuf