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

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!

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.

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

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

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

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...

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!

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.

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!

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

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

That worked! Thanks again!

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...