How to deal with manual entry and formula Simultaneously in a single cell ?

[Vimal Bhatt]

Hi Friends !
I put two problems facing while working in excel 2007.

[1] Sometimes I use a cell with formula as well as manual entry.e.g. I use B1 with today() function and when I need another date, I enter manual date and the formula, evidently, disappears.I don't know if It is possible when we make manual entry ,the cell will take that entry and when delete the entry, the cell will take value from formula.Presently, I use two cells for this. e.g. B1 take value from formula ( today() ) when A1 is empty.If I enter manual date in A1 , the B1 will take value form it and the formula will not wipe out.
Is it possible to use only single cell this way ?

[2] Can I lock particular cell by formula or UDF ?

I have always got the perfect solution from the masters of Excel-community. So thanks in advance.

[JohnTopley]

Do you mean this ..

in B1 =IF(A1="",TODAY(),A1)

???

[gmr4evr1]

Answers to [1] You can not have a formula AND manual entry in the same cell, it can only be one or the other.

[MarvinP]

Hi Vimal,

If you have a worksheet function (like Sum or VLookup) in a cell you can't simply go into that cell and type a value over it. The value (like 10 or 55) will replace the formula/function in that cell.

I'd suggest you use some VBA to put today's date in the cell. By using VBA you aren't keeping a function in the cell.

Here is the code and attached example:

Please Login or Register  to view this content.

[TMS]

Not with a formula, and not with a UDF. However, you can probably do it with a Worksheet Change event handler PLUS a Worksheet Selection Change event handler.

Please Login or Register  to view this content.

Regards, TMS

[Vimal Bhatt]

Thank you friends, thanks all of you,

Now I will go for options one by one,guided by you, and will see what happen

Thanks for sharing my problem

[Vimal Bhatt]

Thanks ,friends, my job has done,special thanks to MarvinP

One question to TMS as curiosity
I appreciate ,if you can spare some time to drop few lines how to use event handler you mentioned in Excel file
for I am not much familiar with VB.

[ChemistB]

VBA code is often used within a macro that is activated when you push a virtual button or start it from the macros window. You can also have the code activated by a particular event (such as changing a value, double clicking on a cell, or activating a worksheet). To see these options, right click on the spreadsheet tab, >View Code. This will open up the VBA editor and you'll be inside the Worksheet module. Change the dropdown menu that currently says "General" to "Worksheet" The dropdown menu to the right has a number of "events" that you can choose to trigger your VBA code. That is an event driven macro or event handler code. Hope that helps.

[Vimal Bhatt]

Thanks ChemistB !
You deliver a great tips. I learn a lot more regarding use of codes in excel
Thanks again

[Vimal Bhatt]

My second Point "Can I lock particular cell by formula or UDF ?"
If anyone have reply, pls let me know.

[ChemistB]

If you are asking if you can lock a cell that has a formula or UDF in it so that no one can type over it (or change it), then yes.

If you are asking if you can, without locking the cells, alllow only formulas or UDF's into a cell, then I think that would require VBA.

WHich are you asking?

[Vimal Bhatt]

I put the question as example.

Say A1 is not locked ( and sheet is not protected)
If B2 has "one type" value,the A1 required data from user and A1 should not be locked
if B2 has "second type" value, the A1 required nothing and must be locked and if data entered in it by mistake, it spoils another linked sheets.
So how to lock and unlock A1 without protecting sheet ?

[ChemistB]

Let's say if B2 says "auto" then we want A1 to lockup.

Using Data Validation on A1, Custom>
Formula: =$B$2<>"auto"
However, they can change A1 before B2 becomes "auto" or
they can copy and paste into A1 which bypassees the data validation

[Vimal Bhatt]

It's certainly useful.But can I lock ( or unlock ) cell without protecting sheet? By VBA or else, just as we lock the cell when sheet is protected.

[ChemistB]

Not sure what you are asking. See this example. A1 is locked unless B1 reads "manual"
Unfortunately Copy and Paste will screw up everything but Excel wasn't made to work this way.

[Vimal Bhatt]

Thanks ChemistB and all !
I think I will do the job with formule and data validation as you rendered.I think you are right,the excel can't do things for which it is not made.
I think my problem is slolved