Use only number in a alpha numeric cell & Data Validation

**nd4spd** · 05-18-2011, 06:43 PM

Cell J1 Header

HTML Code:

Cell J2 formula

HTML Code:

shows error #VALUE! how can i retain the numeric character in J1 and keep description VAT without altering its format.

Column L has Data Validation "List" how can i get predictive text options after inputing few characters. for example if the list contains Sundry Debtors, Sundory Expenses, Sundry Creditors, Sales and Sundry is inputed on L2 it should provide me 3 options to select from.

**Richard Buttrey** · 05-18-2011, 07:19 PM

Hi,

Please Login or Register  to view this content.

Regards

**nd4spd** · 05-18-2011, 07:26 PM

Thanks Richard

HTML Code:

**nd4spd** · 05-19-2011, 08:30 AM

could anyone help me in resolving the second issue on post 1 ??

Column L has Data Validation "List" how can i get predictive text options after inputing few characters. for example if the list contains Sundry Debtors, Sundry Expenses, Sundry Creditors, Sales and Sundry is inputed on L2 it should provide me 3 options to select from.

**Richard Buttrey** · 05-19-2011, 05:29 PM

Hi,

I don't believe predictive text is available with Data Validation.

I had to write a VBA procedure to mimic this. The essentials of which were to use a data filter advanced to filter a known list of values using the input cell characters with a "*" either side in a criteria range so that the filter outpust any values that contained those characters anywhere in the string.

This output list is the basis of a dynamic range name which in turn is used as the RowSource property for a ListBox on a user form. A selection can then be made from the ListBox if required and the value selected then placed back in the input cell.

Hope this gives you some ideas.