+ Reply to Thread
Results 1 to 13 of 13

Formula and Manual Input

  1. #1
    Registered User
    Join Date
    01-14-2013
    Location
    Dover
    MS-Off Ver
    Excel 2003
    Posts
    16

    Formula and Manual Input

    Is it possible to enter a formula in a cell that if some-one by accident manually enters data in the wrong cell, and deletes the cells formula. It will re-populate in the cell automatically?? Protect the formula in the cell but also allow manual data input.

    I can't protect the cell as it is a time sheet and people finish earlier and later than scheduled.

    Many Thanks.

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula and Manual Input

    not sure what you mean..if a cell contains a formula, why should anyone be able to overwrite it?...if you have the cells for input unlocked, then lock the worksheet, they will only be able to input data into the cells you unlocked
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    01-14-2013
    Location
    Dover
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Formula and Manual Input

    It's a time sheet and the end of shift times auto populate from say cell E16 and enters 21:00 in G16 but we sometimes get back late or early, so say we finish at 21:45. So the end of shift time needs to be entered manually otherwise we don't accrue the time.

    So say somebody entered the end of shift time in cell G16 but it should of been in G17 when they delete the time I lose the formula.

    But I need the formula to re appear in cell G16??

    Hope this helps??

  4. #4
    Registered User
    Join Date
    11-16-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Formula and Manual Input

    Can you have an automatic cell (with formula), a manual cell, and a third cell that picks the manual cell if it is used, the automatic cell if not.
    [i.e.-=if(G17<>0, G17, G16)]. then you could lock the first and third cell and still have the option for user input.

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula and Manual Input

    Could you upload a sample workbook?, think you would need a UDF to accomplish it the way you want, but maybe you could add an extra column for manual shift ends, then if that column contains a value, use it, else auto-populate ?

  6. #6
    Registered User
    Join Date
    01-14-2013
    Location
    Dover
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Formula and Manual Input

    Please find attached the copy of the workbook I am working on, as I said previously its cells F12 and G12(Daily) that concerns my queries. Any help would be very much appreiciated.

    Many Thanks.
    Attached Files Attached Files

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula and Manual Input

    See if this is something you can work with:time sheet.xls

    to unprotect the worksheet, just hit unprotect sheet, there is no password, just used it for testing
    also modified the code in daily hrs column to reflect the new situation

    Hope this helps

  8. #8
    Registered User
    Join Date
    01-14-2013
    Location
    Dover
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Formula and Manual Input

    Thanks for your efforts but it isn't really what I was after. I was hoping just to have the two columns and manually type over the top and if they deleted the data then the formula would come back into place?? Is this possible?? Think I have seen this in another spreadsheet but I am unable to remove the protection to see how they done it??

    Any other help would be very much appreiciated??

    Thanks.

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula and Manual Input

    It's probably possible using a macro/sub, but that is beyond my level of VBA skills at the moment, I'll see if anyone else is up to it though

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Formula and Manual Input

    right-click the 'daily' worksheet tab, select 'view code' and paste in this code
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  11. #11
    Registered User
    Join Date
    01-14-2013
    Location
    Dover
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Formula and Manual Input

    Thank you very much JosephP and dredwolf this seems to work a treat. Just one more question, will this work all the way down the workbook????

    Many thanks for your help.

  12. #12
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Formula and Manual Input

    yes it works from row 12 down to the bottom of the sheet

    please don't forget to mark the thread solved

  13. #13
    Registered User
    Join Date
    01-14-2013
    Location
    Dover
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Formula and Manual Input

    Thanks for your help again.

+ 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