Remove blanks in data validation lists

[ajnaaslam]

I have a sheet which is generated as below (all cells contain formula):

I have drop-down lists for Construction, PSA etc. in different sheets values of which are taken from the below table.

I have named A2:A100 (in the below table) as "construction_filter" and is referring to this in a data validation list. How do I get the list to ignore blanks? (blanks in between values, and blanks towards the end of the list)

As said above, all cells contain formula since it is generated from a different sheet that has all the information together.

Tried many formlae to remove the blanks , but can't get anything to work. Please help me with this! Thank you!

Construction PSA Secondment Design-Build DBOM Supply Service (IT) PWA/GTC/102/13-14 MRPSC/018/13-14/L PWA/ITC/016/14-15 PWA/WPC/191/14-15 PWA/GTC/075/14-15 PWA/GTC/072/14-15/L PWA/STC/040/14-15 PWA/STC/039/14-15 PWA/GTC/045/14-15 PWA/GTC/076/14-15/DO PWA/ITC/035/14-15 PWA/GTC/021/14-15 PWA/ITC/030/14-15/L PWA/ITC/024/14-15 PWA/ITC/032/14-15 PWA/GTC/085/12-13/L <colgroup><col span="6"></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[L. Howard]

If using a couple helper columns is not a problem, take a look here.

Howard


http://blog.contextures.com/archives/2014/02/27/dynamic-list-with-blank-cells/

 

[ajnaaslam]

I don't have enough words to thank you Howard! The solution provided by you works great! I have been trying to solve this since the past two days with no luck. Thank you very much!


Ajna

 

[L. Howard]

You're welcome, glad it works for you.

That site seemed to be pretty easy to follow and described you situation quite well.

Howard