Protecting formulas but allowing data entry

[Alex Ondrick]

In advance, I'd like to apologize in the event I've created a duplicate thread. I had spent some time searching around the forums for a similar problem or solution, but I was not able to find anything. Either that, or I'm just a bad searcher

Anyway, I have a spreadsheet here for a form. There are cells which have formulas in them, which connect to other "IF" statements. However, I want to allow for data entry into said cells in the event that the dropdown bar does not contain the data needed.

Here's the kicker: If possible, the cell would accept new data entered. But if the data is removed, then it reverts back to the formula that was previously entered.

Thanks in advance my comrades!

 

[Alex Ondrick]

Bump!

 

[Alex Ondrick]

...Bump of desperation :/

 

[PaddyD]

give us more info on the cells in question & the formulas in them & the the mechanics of what is going on.

 

[erik.van.geit]

Hi, Alex,

seems nobody likes this one, although you are bumping soon
let me try

what do you mean by

in the event that the dropdown bar does not contain the data needed.

Here's the kicker: If possible, the cell would accept new data entered. But if the data is removed, then it reverts back to the formula that was previously entered.

is this a possible scenario ?
put value in a cell which has a formula
close the file
delete the value ==> formula will be filled in again

can you clarify on where those formulas are ?

couldn't you instead of this procedure do something like in the spreadsheet below ?
formula D2 is comparing B2 with A2, but if there is a value in C2, B2 will be compared to C2 to get the result
D2: =IF(IF(C2="",A2,C2)< B2,"ok","error")

Map1
	A	B	C	D
1	formula		input	result
2	9	11	12	error
3	4	5
4	5	6
Blad1

kind regards,
Erik

 

[Alex Ondrick]

Erik and Paddy, thanks for replying.

To clarify, I am trying to preserve the formula: =IF(A21>1,VLOOKUP(A21,look,2,0)," ")

On occasion, I would like to be able to enter data into the cell containing that formula. However, as soon as I enter text into that cell, the formula disappears. If possible, I am trying to preserve that formula by either hiding it or protecting it so that if the "custom text" entered into the cell is deleted, the formula re-appears.

I hope that clarifies my problem

If it is still too vague, I apologize and will try to clarify more if needed.

 

[PaddyD]

In general, a cell contains either a formula or data, not both. You would be much better advised to set up you sheet to have an area for data entry, an area for formulas, and construct the latter to cope with what you need than try to use vba to gerrymander excel into processes in conflict with its basic design.

Eric has given an example of exactly that - if there's a value in a data entry cell, the formula does one thing, otherwise it does something else.

that said, the vba could still be done, but it's not really my thing...

 

[Alex Ondrick]

Gotcha...I'll try and rig it with the formula in another cell and just wire a cell for data entry to tie with the other cell if needed.

I was basically trying to give the user the option of entering custom text into a dropdown bar if the number/item they needed wasn't listed under the dropdown bar.

Thanks for all the help again!

Let me know if you have any ideas or better ways on how to accomplish something like that :/

 

[PaddyD]

"I was basically trying to give the user the option of entering custom text into a dropdown bar if the number/item they needed wasn't listed under the dropdown bar. "

You can set up data validation to give users a list of options but still let them type something into the cell - just set up the data validation as normal, but on the 'error alert' tab, de-select the show error message option.

 

[Alex Ondrick]

Unfortunately, even with the error messages disabled, the formula is gone after new data is put in. As mentioned before, it's probably not the best idea to have the two different types of entry/data in one cell.

On a whole, I probably should of made this form in VB or something of the sort. It's going to be used-reused type of thing for sales quotes and the like. At the least, I'll just post a read-only file to the master server and let people exhaust the 5 available lines of pre-filled formulas that correspond to dropdown bars. If they want to put in custom data after deleting the formulas, they'll just have to re-open the sheet :D

By the way, if you can't already tell, I'm a sales-marketing guy with a very limited knowledge of Excel