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.