**Question description:**

*This user has given permission to use the problem statement for this blog.*

I want to display a formula in a cell only if another cell equals a specific word, and if that cell does not equal that word I don't want the formula to be in action. This is because when the cell is not displaying that word, the cell that the formula is written in has been conditionally formatted to change.

Solved by K. U. in 33 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
04/05/2018 - 01:39

Hi

User
04/05/2018 - 01:39

hi

Excelchat Expert
04/05/2018 - 01:40

Welcome to Got it Pro

User
04/05/2018 - 01:40

thank you

Excelchat Expert
04/05/2018 - 01:40

Can you paste some sample data so that i can help you with formula

User
04/05/2018 - 01:42

my document has alot of formulas so can i just try to explain it in words

Excelchat Expert
04/05/2018 - 01:42

ok

Excelchat Expert
04/05/2018 - 01:42

Please tell

User
04/05/2018 - 01:43

okay so, my cells have been formatted to change when the load type changes

Excelchat Expert
04/05/2018 - 01:43

ok

User
04/05/2018 - 01:44

so the cell that says weight will be replaced by something else when the load changes from dead to another type of load

Excelchat Expert
04/05/2018 - 01:44

Ok

User
04/05/2018 - 01:44

the load is selected from a drop down menu by the way

Excelchat Expert
04/05/2018 - 01:44

I understand

Excelchat Expert
04/05/2018 - 01:44

Where do you want the formula based on what conditions

User
04/05/2018 - 01:45

now i want the cell C9 to equal a certain formula only if B9 is displaying "Weight (N)"

User
04/05/2018 - 01:46

and if B9 does not say "Weight (N)" i dont want the formula to be there anymore because there will be another word there that the user has to enter their own number

Excelchat Expert
04/05/2018 - 01:46

Formula will be there but it will now show any value that is what i think you want

Excelchat Expert
04/05/2018 - 01:47

what is the formula that you want when it is weight

Excelchat Expert
04/05/2018 - 01:47

Tell me..i will make formula on top of that

User
04/05/2018 - 01:47

=IF(K16=Q18,B12*K9*L20*9.81*10^-9,

User
04/05/2018 - 01:48

i didnt know what to write for the IF False part because i dont want to to be there if it doesnt meet the conditions

Excelchat Expert
04/05/2018 - 01:48

This formula will be applicable in your dataset

Excelchat Expert
04/05/2018 - 01:48

See cell C10

Excelchat Expert
04/05/2018 - 01:48

I have put a formula

Excelchat Expert
04/05/2018 - 01:48

IF cell B9 is having weight (N) then 1000 otherwise blank

Excelchat Expert
04/05/2018 - 01:49

in place of 1000 we can put formula as well

Excelchat Expert
04/05/2018 - 01:49

Let me know of that helps

User
04/05/2018 - 01:49

hmm let me try to get my head around that

Excelchat Expert
04/05/2018 - 01:49

ok

User
04/05/2018 - 01:50

but i want the user to enter any number they want if weight is not displayed

Excelchat Expert
04/05/2018 - 01:50

But if you enter formula in cell C9 you won't be able to enter value manually

Excelchat Expert
04/05/2018 - 01:51

It will work on that formula

Excelchat Expert
04/05/2018 - 01:51

a cell can either have formula or enter value manually

Excelchat Expert
04/05/2018 - 01:51

Do you get what i am saying?

User
04/05/2018 - 01:51

so it cannot have both?

User
04/05/2018 - 01:51

under any conditions?

Excelchat Expert
04/05/2018 - 01:51

But that you can have in cell next to it

User
04/05/2018 - 01:51

even if conditional formatted is applied or anythign else

Excelchat Expert
04/05/2018 - 01:52

COnditional formatting will help in highlighting the cell

Excelchat Expert
04/05/2018 - 01:52

Think logically..a cell works on formulas

Excelchat Expert
04/05/2018 - 01:52

For it to run manually you need to remove formulas

Excelchat Expert
04/05/2018 - 01:52

You cannot do both the things in same cell

Excelchat Expert
04/05/2018 - 01:52

You can enter manually but in different cell

Excelchat Expert
04/05/2018 - 01:52

Do you get it?

User
04/05/2018 - 01:52

yeah i understand

User
04/05/2018 - 01:53

let me just ask it in a different way to make sure thats the answer

User
04/05/2018 - 01:54

iF cell B9 = "Weight (N)", i want to display the formula "K16=Q18,B12*K9*L20*9.81*10^-9" but if cell B9 does not equal "Weight (N)" i want the cell not not have any value which gives the user the ability to type any number

User
04/05/2018 - 01:55

so is that impossible?

Excelchat Expert
04/05/2018 - 01:55

SO if that does not have any value but it will still contain that formula

Excelchat Expert
04/05/2018 - 01:55

So if you enter value manually that formula will be gone

Excelchat Expert
04/05/2018 - 01:56

and when the dropdown changes then it wount be able to change the value of the cell

User
04/05/2018 - 01:56

but cant i display a formula in a cell under a certain condition ?

Excelchat Expert
04/05/2018 - 01:56

we are dispalying a formula under certain condition but not in the way you want..you want the formula to vanish

Excelchat Expert
04/05/2018 - 01:56

that won't be possible

User
04/05/2018 - 01:58

ok what about if i write that formula in another cell (that i can hide) and then say IF B9 = "weight (N)" display this value in that specific cell that has been hidden, but if weight is not displayed than i want the user to enter any value

User
04/05/2018 - 01:58

is that possible because the formula wont be in that same cell

Excelchat Expert
04/05/2018 - 01:59

So when wight is displayed it would display the formula in that cell where formula is there

Excelchat Expert
04/05/2018 - 01:59

for Ex: you enter formula in D9 but you cannot write to display value in C9

User
04/05/2018 - 01:59

when weight is displayed it will display the answer which comes from the cell that has the formula

Excelchat Expert
04/05/2018 - 02:00

Whichever cell has formula it can only show the value

Excelchat Expert
04/05/2018 - 02:00

so it to come from that cell you have to cennect the cell

Excelchat Expert
04/05/2018 - 02:00

So the cell will again have the formula

User
04/05/2018 - 02:00

so its basically the same problem

Excelchat Expert
04/05/2018 - 02:01

Yes

User
04/05/2018 - 02:01

hmm

Excelchat Expert
04/05/2018 - 02:01

Hope you understand what i am trying to tell you

User
04/05/2018 - 02:01

yeah i do

Excelchat Expert
04/05/2018 - 02:01

Thanks

Excelchat Expert
04/05/2018 - 02:02

I hope you will give good rating

Excelchat Expert
04/05/2018 - 02:02

I want to help you but the ask is impossible

Excelchat Expert
04/05/2018 - 02:02

I have entered the formula in C9 for your reference

User
04/05/2018 - 02:02

see how E7 is based on a formula

Excelchat Expert
04/05/2018 - 02:03

Yes

User
04/05/2018 - 02:04

are you saying it is impossible to -- IF B9 = weight (n) then C9 = E7, but if false i want the user to enter any value

User
04/05/2018 - 02:04

impossible?

Excelchat Expert
04/05/2018 - 02:04

Yes that is not possible

Excelchat Expert
04/05/2018 - 02:04

Cell c9 will have this if formula

Excelchat Expert
04/05/2018 - 02:05

If user enters the value manually then this formula is gone

Excelchat Expert
04/05/2018 - 02:05

and then when dropdown changes

Excelchat Expert
04/05/2018 - 02:05

It will not show what you wanted

User
04/05/2018 - 02:06

so even if b9 does not = weight(n) anymore then i cannot get c9 to change its formula to number

User
04/05/2018 - 02:06

makes sense

Excelchat Expert
04/05/2018 - 02:06

Hmm

Excelchat Expert
04/05/2018 - 02:07

You can try entering value manually in next cell

Excelchat Expert
04/05/2018 - 02:07

If you see C9 is blank user should enter value in D9

Excelchat Expert
04/05/2018 - 02:07

That should help i guess

User
04/05/2018 - 02:08

okay thats good

User
04/05/2018 - 02:08

do you have any other ways around it

User
04/05/2018 - 02:08

i think i will just go with the enter in another cell

Excelchat Expert
04/05/2018 - 02:08

I think that could be the only way in this scenario

Excelchat Expert
04/05/2018 - 02:08

Yes i guess so too

User
04/05/2018 - 02:08

okay

Excelchat Expert
04/05/2018 - 02:08

Thanks for understanding

Excelchat Expert
04/05/2018 - 02:08

I am happy to help you

Excelchat Expert
04/05/2018 - 02:09

Please do give good rating and reviews

Excelchat Expert
04/05/2018 - 02:09

It really helps

User
04/05/2018 - 02:09

yes of course

Excelchat Expert
04/05/2018 - 02:09

Thanks

Excelchat Expert
04/05/2018 - 02:09

Have a great day

Excelchat Expert
04/05/2018 - 02:09

:)

User
04/05/2018 - 02:09

let me just quickly see if i have anther question about this

Excelchat Expert
04/05/2018 - 02:10

Ok

User
04/05/2018 - 02:10

so i will just enter the value of the weight to the cell D9 when weight is displayed

Excelchat Expert
04/05/2018 - 02:11

ok

User
04/05/2018 - 02:11

and maybe lock cell c9 when b9 = "weight"

User
04/05/2018 - 02:11

thats possible right?

Excelchat Expert
04/05/2018 - 02:12

I dont have much idea into locking the cell

Excelchat Expert
04/05/2018 - 02:12

You can try may be

User
04/05/2018 - 02:12

okay

User
04/05/2018 - 02:12

thank you

User
04/05/2018 - 02:12

ill give a good rating!

Excelchat Expert
04/05/2018 - 02:12

Thanks a lot

User
04/05/2018 - 02:12

:)

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*