+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275

    Question 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

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    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

  3. #3
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275
    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

  4. #4
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275
    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
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275
    Ooooppppps!

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

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    9.5% is really 0.095 so code has to be changed to reflect that so does validation. ive done it on your sheet.
    also macro is a change macro only runs when cell changed (or double clicked on) so it only works if you type in the cell
    (to get around that ,on your sheet i used if(E2=0.095,0,"")
    in F
    dragged it down to put "0" in then copied /paste special values. so the initial zero is set.) so if you now type 9.5% in cell a zero will appear.
    Attached Files Attached Files
    Last edited by martindwilson; 08-16-2008 at 01:54 PM.

  7. #7
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275
    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

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    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

  9. #9
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1