unlocking/clearing cell that is formulated depending on drop down list

[TBL3]

Hi,

My situation is that there is a cell that contains dropdown list. And next to that cell, there are cells that is formulated so it calculates the values desired.

However, I wish to have few exceptions depending on what they choose from the drop down list.
Depending on what the user choose, I wish to make the formulated cells to become unlocked and have no formulas inside those cells for the user to input the values manually.

I have tried with conditional formatting, but I can only make the cells change colours once they have chosen exceptions from the drop down list, but cannot make the fields unlocked for the user to input values manually.

Hence, I was wondering if there is a way for me to do have this functionality in Excel, possibly through using macros.

Any suggestions will be great.


Thanks,
tbl3

 

[TBL3]

I was wondering if this is even possible, and if it is, would ELSEIF do the job?

But I just don't know if a formulated cell can be unlocked with no forumlas and locked with formula depending on what is chosen from the drop down list. Is this possible?

 

[SkipVought]

hi,

Depending on what the user choose, I wish to make the formulated cells to become unlocked and have no formulas inside those cells for the user to input the values manually.

This is TERRIBLE design strategy!

A cell containing a formula ought not to be tanpered with in order to accomodate some other data entry requirement.

Design a logic that enables EITHER!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TBL3]

I know this is not an ideal strategy.
However, I do not have a choice.
The functionalities are already built in, with numerous sheets referencing each other.

The formula for the cells are already contingent to the dropdownlist cell. Depending on what the user chooses, the values defer due to each of the dropdownlist has its unique values and algorithms.

These cells are originally locked, once the sheet is protected for the user to not touch the formulas. I was wondering if there is a way for me to unlock these cells if certain list gets chosen from the dropdownlist. The purpose of the cells to become unlocked is to input the values manually.

Or, if you believe this approach is terrible, how would you tackle this if you are not building it from scratch?

 

[SkipVought]

Well it is obvious that you are intending to significantly CHANGE the design and function of this sheet. So you might as well do it right.

So what about an empty cell for user entry, that we'll name UserValue

Change your formula to test UserValue. If UserValue<>"" then UserValue else wuts there now.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TBL3]

By doing this, I don't understand how a locked cell becomes unlocked or vice versa for user entry.

 

[TBL3]

FYI, there are very limited space to do anything on this sheet.
This is why I was hoping that the fields can be locked or unlocked depending on the dropdownlist cell.

 

[SkipVought]

"Limited space?"

One cell?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Just remove the formula and do the calculation in VB, using the Worksheet_Change event to fine the code to either accept user data or insert the result of a formula.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TBL3]

NVM.

By the way, is there a way to unlock or lock cells? Is this possible in Excel?

Or, I should approach this differently?

 

[SkipVought]

right-click > format > PROTECTION TAB

use your macro recorder.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]