Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc