Firstly I aplogise in advance if my terminology isnt correct
I have created a drop down list that contains 2 choices 'Yes' & 'No' if 'Yes' is selected then i would like it multiply the figure in cell H3 by 2, if 'No' is selected or the drop down list cell is blank then i want the figure in H3 not to be effected.
Any help would be much appreciated!
Many thanks in advance.
Boz
BTW...what a great site and forum, i've learnt so much already, but obviously not enough
=if(A1="Yes",2*H3,H3)Originally Posted by Boz
Firstly I aplogise in advance if my terminology isnt correct
I have created a drop down list that contains 2 choices 'Yes' & 'No' if 'Yes' is selected then i would like it multiply the figure in cell H3 by 2, if 'No' is selected or the drop down list cell is blank then i want the figure in H3 not to be effected.
Any help would be much appreciated!
Many thanks in advance.
Boz
BTW...what a great site and forum, i've learnt so much already, but obviously not enough
where A1 contains your drop down list.
BenjieLop
Houston, TX
Thanks BenjieLop, works GREAT
Would you know how I can change the background colour of just the 'Yes' cell in my drop down list? I have tried to follow the tips on here but need it explained very simply.
Many thanks
Boz
1. Go to the cell where your drop down list isThanks BenjieLop, works GREAT
Would you know how I can change the background colour of just the 'Yes' cell in my drop down list? I have tried to follow the tips on here but need it explained very simply.
Many thanks
Boz
2. Click on "Format/Conditional Formatting"
3. Condition 1 should be "Cell value is"
4. Choose "equal to" in the second box
3. Type "Yes" in the third box (without the quotes)
4. Click on "Format"
5. On the "Color" box, click on the down arrow and the red square
6. Click OK
7. Click OK
Hope this helps ...
Yep...exactly what i was looking for :D
Many thanx again!
Boz
I just thought of another IF function quetsion.
My product excel file has a number of work sheets, in the front sheet i have a column of cells which pull in data from other cells on the same row, examle below:
=CONCATENATE(L220," ",H220," ",M220," for ",G220," replaces OEM ",F220)
Now in some of these cells i want to add the text "***Buy One Get One FREE*** so it looks like this:
=CONCATENATE(L220," ",H220," ",M220," for ",G220," replaces OEM ",F220," *** Buy One Get One FREE ***")
but i ony want the additional text added IF 'Yes' is selected from a drop down list on another worksheet.
Any ideas?
Boz
=if(A1="Yes",Your_Formula1,Your_Formula2)
where
Your_Formula1 is your CONCATENATE formula with the "Buy One Get One FREE" provision while Your_Formula2 is your formula without the "Buy One..." provision.
Hi...I'm having probles putting this formula together because of my lack of understanding of the syntax required. Could you tell me where the errors are in this formula please
=IF('Epson InkJets'!F48="Yes",(CONCATENATE(L216," ",H216," ",M216," for ",G216," replaces OEM ",F216," *** Buy One Get One FREE ***")=CONCATENATE(L216," ",H216," ",M216," for ",G216," replaces OEM ",F216)))
Many thanks in advance!
Boz
Try this ...Hi...I'm having probles putting this formula together because of my lack of understanding of the syntax required. Could you tell me where the errors are in this formula please
=IF('Epson InkJets'!F48="Yes",(CONCATENATE(L216," ",H216," ",M216," for ",G216," replaces OEM ",F216," *** Buy One Get One FREE ***")=CONCATENATE(L216," ",H216," ",M216," for ",G216," replaces OEM ",F216)))
Many thanks in advance!
Boz
=IF('Epson InkJets'!F48="Yes",CONCATENATE(L216," ",H216," ",M216," for ",G216," replaces OEM ",F216," *** Buy One Get One FREE ***"),CONCATENATE(L216," ",H216," ",M216," for ",G216," replaces OEM ",F216))
Hi BenjieLop
Works GREAT
Really appreciate all your help!!!
Many thanks
Boz
Thanks for the feedback ... just glad I can help.
You're very welcome!
how would i nest 3 IF functions in a cell, all text no formulas.
Many thanks
Boz
Your 3 nested IF syntax will be
=if(A1=Condition1,Text1,if(A1=Condition2,Text2,if(A1=Condition3,Text3,Text4)))
which means that if any other condition exists (besides Condition1, Condition2 and Condition3), your cell will return Text4.
Thanx AGAIN
Just realized how long it will take to code all this! Dont think this was set up in the most logical way!
Boz
There are times when it takes you hours to straighten out a formula and all it does is save you a few seconds of your time!!!Thanx AGAIN
Just realized how long it will take to code all this! Dont think this was set up in the most logical way!
Boz
Suggestion ...Thanx AGAIN
Just realized how long it will take to code all this! Dont think this was set up in the most logical way!
Boz
Maybe you can enter
Text1 in CellW1
Text2 in CellW2
Text3 in CellW3
Text4 in CellW4
and modify your nested IF formula to
=if(A1=Condition1,W1,if(Condition2,W2,if(Condition3,W3,W4)))
and you might avoid an Excel message saying that your "formula is too long."
This seems very true!
Boz
Sorry...our replies crossed.
Sounds like a good idea...could i do that with the ***Buy One Get One Free*** text too?
Boz
Yes ... instead of the formula
=if(A1="Yes",CONCATENATE(... with Buy One Get One),CONCATENATE(...w/o BUY))
you can enter in, say,
CellZ1, "Text with the Buy One Get One Provision"
CellZ2, "Text without the Buy One Get One Provision"
and so your formula becomes
=if(A1="Yes",Z1,Z2)
I think this is easier in the long run as you can always change the contents/text in the cells involved instead of messing with the formula itself.
Good luck.
Excellent suggestion!
Many thanx
Boz
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks