+ Reply to Thread
Results 1 to 26 of 26

Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formula

  1. #1
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formula

    Hi,

    I work for a food company and currently am working on a template to allow product developers to create trial paperwork more easily. Many of the formulas in my excel workbook are autopopulated from default data that developers use 90% of the time (things like cook temps, equipment settings, etc).

    There are times, however, when a developer might want to experiment with those default data points (i.e. run a trial to test what happens if they change an equipment setting parameter, such as belt speed). How can I allow them to do this without deleting the formulas that are set as default in the workbook? I.E. if they delete their input to go back to using the default data, I want the default formula to come back. Is there a way to do this? I am using Excel 2010...

    Thanks,
    Sandy

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    You could add a button to your spreadsheet to re-insert the original formulas in those places.

    Then users could freely enter their own inputs, and when done, click the button to restore the original formulas.


    Care to cross the VB line and get your feet wet with a little programming? Something like this shouldn't be terribly complicated. (depending on the intricacy of the formula)
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Hi Daffodil11,

    I don't mind at all using VBA. This template workbook is something that has been needed for a long time. It will be well used! I just wasn't sure that we could use Excel to accomplish what we want...but the only way I'll ever know is to try to figure it out. Any help you can offer would be great! I should add, though, that there are a lot of formulas!

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    As Daff said you'll need to use VBA. Attached is a simple spreadsheet that does what you want (I think).

    The formulas are in column D. I used conditional formatting to change the cell color if any of these don't follow the formula (in this case B+C=D). If the user, clears the cell, or backspaces to clear the cell, the old formula will reappear.

    How it's done:
    An exact copy of sheet one is copied into sheet2. When you enter data into D2:D10, the code looks to see if it's blank. If it is, it goes to that same cell on sheet2 and copies the formula back into sheet1. If you right click on the sheet1 tab>View Code, you will see this
    Please Login or Register  to view this content.
    Questions?
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    LOVE IT, ChemistB. This is exactly what I am looking for. Going to play with it right now in my own workbook. I also like the color change to let the people in the pilot plant know that things are not set up as usual. It gives them a head's up that they need to pay attention.

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Sweet ChemistB. That's totally not how I was going to approach this, and it's like ten bajillion times more efficient. Awesome.

  7. #7
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Ok, help please! How do I apply this code to my page correctly. Say I want to apply it to E3:E100? How would I do that? What about to multiple cells (i.e. the grayed cells)...This is as far as I have gotten...
    screenshot.jpg

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    It looks okay. Do you have formulas in E3:E100 of sheet8? Are you sure there are no spaces before or after the sheet8 tab name? You could upload a sanitized version of your workbook (Go Advanced>Manage Attachments)

    See next post.
    Last edited by ChemistB; 03-13-2014 at 03:46 PM.

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    It looks like you are putting the code into sheet8 and referencing sheet8. It should be in the sheet that people are using and referencing the hidden sheet. Also, it should be whatever you named the sheet, not the default sheet number.

    If that still doesn't work, you can upload a sanitized copy of the workbook (Go advanced>Manage attachments)

  10. #10
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    So, I give up...here is the extremely scrubbed down version. I would like to apply the VB formula to the Template Worksheet in cells C1:F100. The formulas for these are populated from the Standards Worksheet in cells B1:E100. I look forward to your response! Thanks again, both of you.

    PDA Standardization Excel Auto-Populate2.xlsm

  11. #11
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    I think I figured it out. I don't mean to step on your toes ChemistB, but this is a pretty sweet idea and I'd like to make it work too.

    I right clicked on Template tab and made a copy. I named it Template - Hidden.

    Back in VB land I pasted this onto Template's sheet. (the original)

    Please Login or Register  to view this content.

    Then I added Conditional Formatting to C1 as formula: =NOT(Template!C1='Template - Hidden'!C1)

    and then used the Format Painter to spread it everywhere else.

    All that's left is to hide the 'Hidden' sheet. Did I miss anything?
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    That looks right Daffodil. Feel free to step in any time. Now we wait and see if it's working for May.

  13. #13
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    I'm getting ready to give it a try! Wish me luck!

  14. #14
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Ok, I am an idiot.....where is this entered?

    =NOT(Template!C1='Template - Hidden'!C1)

    I don't see it in the formula box on C1, so I must be missing something...

  15. #15
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Nevermind......I googled it and found out where it is supposed to go! LOL...now to apply to my whole workbook. I'll report back in a bit!

  16. #16
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Got it to work! Yay! So is there a way to apply this to drop down lists, too? I can't seem to get that part to work.

  17. #17
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Ok, one glitch....if you type data over multiple cells with formuls, but then delete those cells all at once, the formula does not return. It only seems to work if you delete one cell at a time...sigh...any suggestions?
    Last edited by MayBTheresHope; 03-14-2014 at 11:13 AM.

  18. #18
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Try this code instead
    Please Login or Register  to view this content.

  19. #19
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    As far as drop down boxes (I assume we're talking about DataValidation dropdowns), they don't involve formulas. How do they relate to this problem?

  20. #20
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Thanks, ChemistB! That worked perfectly! I am very excited now.

    As far as drop down boxes, I was just wondering if there was a way to type over a data validation drop down if the user wants to manually input something that is not in the drop down list. Once the user deletes their manual input, however, I would like for the cell to default back to the drop down list.

    Here is an example. I created a drop down list of ingredients, but the developer planning a trial sometimes wants to test a new ingredient that isn't on that list. They want to be able to just type the name of the new ingredient into the cell rather than having to add it to the data validation dropdown list.

    If after running the trial and evaluating the new ingredient in our product, the developer decides to go forward with it, we would then add it to the drop down list. However, if the ingredient did not meet expectations, we would want to simply delete it from the cell and have the drop down box reappear as usual. The reason being is that from trial to trial while working on the same project, the trial planning template is simply modified because much of the information remains the same and there is no real reason to start over with a fresh template. Does this make sense?

    Thanks again for all of your help!

  21. #21
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    The simplest way was to set the data validation error alert to "Warning" instead of "Stop". Then they will just get a warning that the ingredient they selected is not on the list. Do they want to continue? The original list will still be there. No need to use VBA.

  22. #22
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Great to know! Thanks ChemistB and Daffodil11! Problems solved!

  23. #23
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Well, it was working this afternoon...now, not so much. I dont know what is going on. Can one of you take a look at it? I am at a loss...
    PDA Standardization Excel Auto-Populate2.xlsm

  24. #24
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    You added multiple ranges. Maybe give this a shot? My VB jedi skills are pretty weak, but I like trying to debug things.

    Please Login or Register  to view this content.

  25. #25
    Registered User
    Join Date
    03-10-2014
    Location
    Eastern US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    That worked! Thanks again!

  26. #26
    Registered User
    Join Date
    10-04-2018
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    58

    Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formu

    Hello!

    I found this trick and am very excited to try it out. Can anyone help me!? I followed the steps and copied the code directly from here. I am getting the same error as May got in the screen shot above. I can over wright the cell and then when I go to delete the overright (hoping the original info will pop back in) I get a error asking me to debug. When I debug it tells me that this line is incorrect:


    Target.Formula = Sheets("copysheets").Cells(Target.Row, Target.Column).Formula

    I named the sheet just as I named the hidden one...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Allowing a cell with a formula to also allow for user input.
    By joshpit2003 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-24-2013, 03:59 PM
  2. [SOLVED] How do you protect multiple sheets while only allowing users to select unlocked cells?
    By mrssteelerhall in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2013, 02:05 PM
  3. Replies: 9
    Last Post: 12-19-2012, 01:32 PM
  4. Restricting the number of cells users can input data to
    By LittleEm in forum Excel General
    Replies: 7
    Last Post: 11-30-2006, 05:11 AM
  5. [SOLVED] Not allowing users to enter data into certain cells if another cell is empty
    By KimberlyC in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-06-2005, 02:06 PM

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