Can 'Data Validation' allow user input and automated entry?

Hi all

I am now in the process of re-structuring my workbook and have run up against a couple of problems.

The first is on a worksheet that has 6 colums of manual data entry. Column E is formatted as 'percentages'. Column F is formatted as an integer number.

In order to reduce some of the manual entries in Column F; I attempted to use Data Validation to input a zero when the adjacent cell in Column E is equal to 9.5% but to allow a manual entry when it does not equal 9.5%.

I have tried some combinations of IF formulas but without success and there does not appear to be a similar thread when I tried searching for a solution.

Hopefully, someone has come across this problem before.

TIA ...spellbound

try inserting this code in your work sheet

Please Login or Register  to view this content.

it will put 0 in anycell in row in F if corresponding row in e=9.5
then in a cell away from your working area say AA1
put
=IF(E1=9.5,0) and drag down
then in validation for col F

use custom/formula
=AA1<>0

Hi Martin

Thanks for the quick response.

However despite my experience (if you can call it that) with Excel, I have never done any work with code or VBA, everything in my workbook is done by functions and formulas.

So, I am going to have to ask you to explain to me how to insert your code into my worksheet.

spellbound

Back again!

Managed to find out how insert the code into my worksheet and I used Column M for the test and just altered the validation formula to suit.

However, nothing comes up in column F.

I have attached the worksheet in question in case that helps to solve the problem.

spellbound

Ooooppppps!

Apolgies, there is an error in the previous attachment, correct one is with this message.

Hi Martin

Thanks for sorting that out for me, I have it up and running as per your latest instructions, which should resolve some of the data input issues.

Whilst experimenting with it, I did find that there are 2 possible errors that could occur when changing data as Column E is manually inputted at the moment.

For example if you accidentally delete the automated Zero whilst it still shows 9.5%; saving or updating the workbook does not update this cell and leaves the cell blank. The only option is to input the 9.5% again to create the zero but this may go un-noticed.

Likewise if the figure in column E has to be changed to a different value, the automated zero still remains in column F, which again may go un-noticed.

Would you know of any way of adjusting the code or formulas to correct these problems?

Thanks again ...spellbound

heres another code it recalculates for blanks zeros and 9.5%

Please Login or Register  to view this content.

cant see how you cet around removing 0 if you enter somthing >0 in % it would have to clear cell but auto would clear all cells

Thanks for the latest worksheet code but I am still having problems with the Error traps that I mentioned in my last message.

If you delete the automated zero in column F and then press F9 or even try to save the worksheet, the zero does not re-appear in column F. Even if you overtype the value (9.5%) in colum E; the result will not appear in column F.

The only way to make it re-appear is either to delete the 9.5% in column E and type the value in again or wait until a further entry is made in column E and then it will re-appear with F9 or save.

Likewise if the figure (9.5%) in column E has to be changed to a different value, the automated zero still remains in column F, even though the result of the formula in column is now FALSE. Using F9 or even save does not make the result re-calculate itself to reflect the change.

Hope you can help resolve this ...spellbound