
Need Formula For If A Particular Cell "starts With"


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Need Formula For If A Particular Cell "starts With"  Excel

View Answers


Hello,
I am in need of an Excel formula that will say (example) "If cell A1 starts with letter AD, then input "George Washington" into cell D1."
There will be atleast 6 different scenerios needed in the formula.
It will be divided as such:
If A1 starts with AD, cell D1 should be "Mary Jones".
If A1 starts with EH, cell D1 should be "Ann Jones".
If A1 starts with IL, cell D1 should be "James Jones".
If A1 starts with MP, cell D1 should be "Angela Jones".
If A1 starts with QT, cell D1 should be "Melissa Jones".
If A1 starts with UZ, cell D1 should be "Jimmy Jones".
I thank you in advance and as always appreciate all of your help!
Similar Excel Video Tutorials
Delete All Invoices w TBO
 Delete All Invoice Records That Contain an ID with "TBO" using the SEARCH function and Go To Formula Numbers. SEARCH("TBO",&q ...
Formula Not Calculating Stuck
 If your formula does not calculate and is showing the equal sign and the rest of the formula without calculating, here are two possible solutions.
Go To Tricks Complete Story
 See how to highlight all occurrences of an item or Excel element and then change them all quickly See how to how to Use Go To to select all occur ...
Similar Topics
I have this huge formula that I am using to create a field with text in it defining ingredients in food items. Is there anyway I can break it up?
Here's the formula:
Quote:
=C2&IF(SUM(D2:E2)>0," ","")
&IF(D2>0,"Vegetarian","")
&IF(D2=3," on request","")
&IF(SUM(D2:E2)=2," or ","")
&IF(E2>0,"Vegan","")
&IF(E2=3," on request","")
&IF(SUM(D2:E2)=2," on request","")
&IF(SUM(F2:O2)>0," contains (","")
&IF(P2=1," May Contain","")
&IF(F2>0,"Alcohol","")
&IF(AND(G2=1,H2=0,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(Q2=1," May Contain","")
&IF(G2>0," Dairy","")
&IF(AND(H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(R2=1," May Contain","")
&IF(H2>0," Eggs","")
&IF(AND(I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(S2=1," May Contain","")
&IF(I2>0," Gluten","")
&IF(AND(J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(T2=1," May Contain","")
&IF(J2>0," Soy","")
&IF(AND(K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(U2=1," May Contain","")
&IF(K2>0," Peanuts","")
&IF(AND(L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(V2=1," May Contain","")
&IF(L2>0," Nuts","")
&IF(AND(M2=1,N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(W2=1," May Contain","")
&IF(M2>0," Seeds","")
&IF(AND(N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(X2=1," May Contain","")
&IF(N2>0," Fish","")
&IF(AND(O2=1,SUM(F2:O2)>1)," &","")
&IF(Y2=1," May Contain","")
&IF(O2>0," Shell Fish","")
&IF(SUM(F2:O2)>0,")","")
I am trying to copy an entire row that starts with the word "HDR" in the cell to all the rows below it that start with "DTL" until it reaches another row that starts with "HDR". Once it reaches another row that starts with "HDR" it will copy the row "HDR" to all the rows below it that start with "DTL" till it reaches a row that starts with "HDR" again.
For example if C1 is "HDR123", C2 is "DTL123", C3 is "DTL345" and C4 is "HDR456" it would copy row C1 to B2 and B3. I would then copy C4 to whatever rows below it start with "DTL". Any help would be greatly appreciated.
Thanks,
I am looking to write the following as a function
If cell D1 is "John" AND cell D2 is "Smith" OR "Jones" OR "Watkins" D3 = "Option 1"
If cell D1 is "William" AND cell D2 is "Smith" OR "Jones" OR "Watkins" D3 = "Option 2"
and a number of alternatives.
Can anyone show me how to do this sort of thing, or provide a link to examples, or provide a tool to work this out.
Thanks!!
Dave
Hello,
I have this formula in all the cells in column W:
=IF(ISERROR(IF(V4="Disco",0,IF(V4="A*",1,IF(AG4="D",0,IF(AP4="*disco*",0,IF(V4$N$1>2,1)))))),0,IF(V4="Disco",0,IF(V4="A*",1,IF(AG4="D",0,IF(AP4="*disco*",0,IF(V4$N$1>2,1,))))))
The intent of it is to put a 1 or a 0 in that cell so that a pivot table reading it can be filtered to calculate based on only those with a 1.
Sometimes the value in the cell in colum V is text and if so it always starts with "A" but could have anything after the "A" so I tried to say if it starts with "A" followed by anything (thus my "A*"), give me a 1.
Similarly, in column AP, the cell is text and may or may not contain the word "Disco" but if it does I'd like W to be a 0.
The sheet is 12,000 plus rows and works fine except the value it generates for column W doesn't seem to be influenced at all by the text values I've tried to include for columns V or AP, so I currently have to filter for those an manually input the 1 in each cell.
Any advice on this would be much appreciated. (not as much as sound advice on how to make money in today's markets , but definately appreciated! Thanks!!
I need a macro that copies rows only if certain text is in column A
For example if Cell "A5" is Evan Jones, then Cells B5J5 will get
copied to the sheet named "Evan Jones", and if another cell in column A
also says Evan Jones then the same applies for its row except it will
be put in the next row of "Evan Jones" sheet.
I have several names that this will be working with in "Column A" which
is titled "Customer Name", and each line will have information in
columns BJ for those rows....
I hope you understand what I mean, and I hope someone can help....
Thanks
I have some data on a spreadsheet. An example follows.
Mr John Smith, "1 Main Street", "london", "", "NW1 1AA", "UK", ""
Mr David Jones, "1 High Road", "Cardiff", "", "C1 1AA", "UK", "W"
Mr Ian Nesbit", "1 Hill Road", "Alexandria", "Dumbartonshire", "", "", "S"
All the data is in cell A.
Is there a way to split the data? As it is in ""'s and even where no data is available there are " " to show that some info is missing.
Any help appreciated.
I am using xlsx (Excel 2007)
In the original setting of excel, the column name starts from "A", "B", "C",
.....so on and the row name starts from "1", "2", "3".... so on. Is it
possible to change the setting to the column name starts from "1", "2", "3"
and the row name starts from "A", "B", "C" ?
Many thanks in advance !
Hello all,
I am looking for a way to change the color of a cell based on the result. I have tried conditional formatting. What I need is a little more complicated though.
I am assuming that I need an "IF" formula, but I am not sure how to do it. I'm teaching myself formulas as well as VBA at the same time, and I think I am confusing myself.
What I need is: For the cell formula to look at another cell's value and see what letter that it's value starts with. Then I need it to look at it's own value and then based on those two conditions adjust the color of the cell acordingly.
Example: My if formula is in A1 and it is looking at C1. If C1's value starts with say a "P" then look at A1's value. If it is between "0MONTHS" and "6MONTHS", then change the color of the A1 cell green.
I also already have another formula in A1. It is:
Code:
=N1&""&O1
Hope I explained that well enough.
Thank you all in advance.
I need a macro that copies data only if certain text is in column A
For example if Cell "A5" is Evan Jones, then Cells B5J5 will get
copied to the sheet named "Evan Jones", and if another cell in column A
also says Evan Jones then the same applies for its row except it will
be put in the next row of "Evan Jones" sheet.
I have several names that this will be working with in "Column A" which
is titled "Customer Name", and each line will have information in
columns BJ for those rows....
I hope you understand what I mean, and I hope someone can help....
Thanks
Hi Excel gurus, I have a formula that says too long, heres my formula...
=LOOKUP(B141,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32 ,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65, 66,67,68,70,71,72,73,74,75,76,77,78,79,80,81,494,495,496,497,498,499},{"$11.00","$11.00","$9.50","$8 .00","$8.50","$15.00","$12.00","$21.50","$14.50","$21.50","$22.50","$18.00","$21.50","$26.50","$14.0 0","$14.50","$14.50","$19.50","$14.50","$14.50","$19.50","$9.50","$14.50","$28.50","$42.00","$40.00" ,"$14.50","$24.00","$18.00","$24.00","$9.00","$7.50","$42.00","$30.00","$33.50","$45.00","$25.00","$ 23.50","$21.00","$14.50","$7.00","$21.50","$23.50","$33.50","$9.00","$8.50","$21.50","$36.00","$36.0 0","$36.00","$8.00","$33.50","$30.00","$50.00","$58.00","$72.00","$12.00","$6.00","$21.00","$7.00"," $72.00","$30.00","$7.00","$15.00","$10.00","$35.00","$36.00","$40.00","$7.00","$3.00","$8.00","$25.0 0","$3.00","$4.50","$1.50","$6.00","$12.00","18","20","16","$25.00","$28.00","$25.00","$35.00","$25. 00","$18.00"})
Enter PLU here qty entered
81 16
79 18
39 21
the problem is I will have a lot more PLU numbers as time goes. No wI am stuck, please help thanx in advance...
I have two worksheets in two different workbooks  the worksheets are
named "EARNED" and "Pay App Qty's". Each of these worksheets are
fairly complex and pull values automatically from OTHER worksheets
contained within their respective workbooks.
Column A in both worksheets contains specific Item Numbers  what I
need is code that will compare column A of both worksheets and when a
match is found copy a quantity from the appropriate cell in Column Z of
"Pay App Qty's" and paste it into the appropriate cell
in Column C of "EARNED".
"Pay App Qty's" contains multiple hidden rows  ONLY the visible rows
need to be included in the comparison.
Column Z of "Pay App Qty's" pulls figures from other worksheets within
it's workbook.
Item Number data starts in cell A7 of "Pay App Qty's".
Quantity data starts in Cell Z7 of "Pay App Qty's".
Item Number data starts in cell A2 of "EARNED".
Quantity data starts in cell C2 of "EARNED".
Data in "Pay App Qty's" currently goes to row 446.
Data in "EARNED" currently goes to row 271.
I am using Excel 2002 SP2 and running Windows XP Professional.
Any and all help is sincerely appreciated.
Thanks!
 Kobi
I'm looking to create a mailto link. The issue i'm having is with the 255char limit i believe. I'd like to enter quite a few email addresses so that they'll automatically be entered into outlook when I click on the link. Is there a way to do this? all the addresses come from a different row of a column. I've concatenated them all with a comma delimiter so that formula can be used to enter after the mailto.
What I have:
=hyperlink("mailto:" & =$K$12&IF($K$13<>"",","&$K$13,"")&IF($K$14<>"",","&$K$14,"")&IF($K$15<>"",","&$K$15,"")&IF($K$16<>"" ,","&$K$16,"")&IF($K$17<>"",","&$K$17,"")&IF($K$18<>"",","&$K$18,"")&IF($K$19<>"",","&$K$19,"")&IF($ K$20<>"",","&$K$20,"")&IF($K$21<>"",","&$K$21,"")&IF($K$22<>"",","&$K$22,"")&IF($K$23<>"",","&$K$23, "")&IF($K$35<>"",","&$K$35,"")&IF($K$36<>"",","&$K$36,"")&IF($K$37<>"",","&$K$37,"")&IF($K$38<>"",", "&$K$38,"")&IF($K$39<>"",","&$K$39,"")&IF($K$40<>"",","&$K$40,"")&IF($K$41<>"",","&$K$41,"")&IF($K$4 2<>"",","&$K$42,"")&IF($K$43<>"",","&$K$43,"")&IF($K$44<>"",","&$K$44,"")&IF($K$45<>"",","&$K$45,"") &IF($K$46<>"",","&$K$46,"")&IF($K$47<>"",","&$K$47,"")&IF($K$48<>"",","&$K$48,"")&IF($K$49<>"",","&$ K$49,"")&IF($K$50<>"",","&$K$50,"")&IF($K$51<>"",","&$K$51,"")&IF($K$52<>"",","&$K$52,"")&IF($K$53<> "",","&$K$53,"")&IF($K$54<>"",","&$K$54,"")&IF($K$55<>"",","&$K$55,"")&IF($K$56<>"",","&$K$56,"")&IF ($K$57<>"",","&$K$57,"")&IF($K$58<>"",","&$K$58,"")&IF($K$59<>"",","&$K$59,"")&IF($K$60<>"",","&$K$6 0,""))
I've also tried to do things like display all the emails so that they can be copied to the clipboard after the excel file has been saved and then viewed as a webpage. This runs into the 1024char limit when leaving the concatenated addresses in a cell or even a text box. any fancy idea on a way to accomplish any of this?
Tom
Hi everyone. I need a little help. I have a column full of text. Some cells start with a " " (Hyphen space) others do not. There could be other hyphens throughout the cell but I am only interested in if starts with " " Here is an example of a few cells
Cell # text in cell
aq56  Cortelco  Volume  Tone/pulse  Ringer
aq57 Cortelco  Volume  Tone/pulse  Ringer
See how aq56 started with a " " but aq57 does not
I am going to be taking out the " " with =MID(AQ56,3,65000) but if the cell doesn't have the " " then it ends up stripping out the first to characters. Basically I need some sort of IF formula that says IF the cell STARTS with " " then do the MID function, otherwise just copy the cell. I just want to be sure that the formula doesn't pay attention to the later occuring " ". I am guessing a LEFT function would work but I am brain locked and can't get a working formula out.
I prefer this to be a formula, not a VBA. I will probably be adding other conditions later and I can usually build off a formula but i dont know programming at all.
Thank you so much for your help!!!
Eric
Help!
I have a stupid long forumla that needs to be longer! There must be an easier way of doing and I'm hoping one of you guys can sort it.
=IF((AND(AF4="w",AG4="w")),"0")*(IF((AND(AF4="w",AG4="1c")),"1"),(IF((AND(AF4="w",AG4="1b")),"2",(IF ((AND(AF4="w",AG4="1a")),"3",(IF((AND(AF4="w",AG4="2c")),"w",(IF((AND(AF4="w",AG4="2c")),"4",(IF((AN D(AF4="1c",AG4="w")),"1",(IF((AND(AF4="1c",AG4="1c")),"0",(IF((AND(AF4="1c",AG4="1a")),"1"),(IF((AND(AF4="1c",AG4="1a"))," 2"),(IF((AND(AF4="1c",AG4="2c")),"3"),(IF((AND(AF4="1c",AG4="2b")),"4"),(IF((AND(AF4="1c",AG4="2a")) ,"5"),(IF((AND(AF4="1b",AG4="w")),"2"),(IF((AND(AF4="1b",AG4="1c")),"1"),(IF((AND(AF4="1b",AG4="1b")),"0"),(IF((AND(AF4="1b",AG4="1a")),"1"),(IF((AND(AF4="1b",AG4="2c")) ,"2"),(IF((AND(AF4="1b",AG4="2b")),"3"),(IF((AND(AF4="1b",AG4="2a")),"4"),(IF((AND(AF4="1b",AG4="3c" )),"5"),(IF((AND(AF4="1a",AG4="w")),"3"),(IF((AND(AF4="1a",AG4="1c")),"1"),(IF((AND(AF4="1b",AG4="2b")),"3"),(IF((AND(AF4="1b",AG4="2a")),"4"),(IF((AND(AF4="1b",AG4="3c")) ,"5"),(IF((AND(AF4="1a",AG4="w")),"3"),(IF((AND(AF4="1a",AG4="1c")),"1"))))))))))))))))))))))))))))))))))
Any ideas?
Hi everybody,
I'm having a bit of trouble finding whether or not a hyphen is present in a string. Basically I want to split up a string of names into first name, last name. However, for doubled barreled surnames (such as "WynneJones"), the code recognises the "" as a " ", and instead of putting the last name as "WynneJones", it lists it as "Jones".
Does excel have problems finding hyphens in strings? Are there any ways around it?
I would like to combine an IF formula with a VLOOKUP.
The problem is I would like it to only look at the first Letter in the Cell.
If the Cell starts with an "F" then.... (F12562)
If the Cell starts with a "U" then..... (U2568)
if it makes a difference sometimes the code I'm inputting is
a Letter & 5 numbers, and sometimes a Letter & four numbers.
I must be missing something cause it can't be this frustrating.
In column D are names (for example):
Jones, Bryan
Lennon, John
Harrison, George
Column C is empty
I want column C to look like this:
Jones, B.
Lennon, J.
Harrison, G.
Is there a formula that will make this happen. I'm sick and tired of manually deleting all of this data every month. Please help.
Thanks.
I am looking how to compose a formula that states, "Sum Product if (sheet1!="MBA)" and (sheet 2! starts with "MBA"
Can someone please assist? I thank you in advance!
Hi,
I am trying to create a spreadsheet that contains several fitness tests.
For example, (this is hypothetical) let's say I want to measure a pushup test. Both males and females have different ranks for different age groups... you can see how this starts getting complicated and long (too long for the cell, so the error message pops up) i.e.: Females, between the ages of 2029 who do 2129 pushups are considered "Very Good" and Males between the ages of 2029 who can do between 2935 are considered "very good". Here is my code thus far for this data set.
=IF(AND(B27>=MIN(20),B27<=MAX(29),B26=("F"),B25>=MIN(20), B25<=MAX(29)),"Very Good","")&" "&IF(AND(B27>=MIN(30),B27<=MAX(39),B26=("F"),B25>=MIN(19), B25<=MAX(26)),"Very Good","")&" "&IF(AND(B27>=MIN(40),B27<=MAX(49),B26=("F"),B25>=MIN(14), B25<=MAX(23)),"Very Good","")&" "&IF(AND(B27>=MIN(50),B27<=MAX(59),B25>=MIN(10), B25<=MAX(20)),"Very Good","")&" "&IF(AND(B27>=MIN(60),B27<=MAX(69),B26=("F"),B25>=MIN(12), B25<=MAX(16)),"Very Good","")&" "&IF(AND(B27>=MIN(20),B27<=MAX(29),B26=("m"),B25>=MIN(28), B25<=MAX(35)),"Very Good","")&" "&IF(AND(B27>=MIN(30),B27<=MAX(39),B26=("m"),B25>=MIN(21), B25<=MAX(29)),"Very Good","")&" "&IF(AND(B27>=MIN(40),B27<=MAX(49),B26=("m"),B25>=MIN(16), B25<=MAX(24)),"Very Good","")&" "&IF(AND(B27>=MIN(50),B27<=MAX(59),B26=("m"),B25>=MIN(12), B25<=MAX(20)),"Very Good","")&" "&IF(AND(B27>=MIN(60),B27<=MAX(69),B26=("m"),B25>=MIN(10), B25<=MAX(17)),"Very Good","")
My question is: Is there any shorter way to do this type of data set? To do this for 5 different ranks, for 5 different age groups can get lengthy and definitely not fit into one cell, which would be helpful for me.
I've tried to search all over the internet, but I can't seem to get the description correct.
Hi all,
I have a several workbooks that contains multiple sheets for computing Annual Leave of staff.
All books have the same standard sheets named "Main", View" "Annual", and "Sick"
and then a sheet per person.
The VBA code currently uses as array like
Code:
Sheets(Array("SMITH", "JONES", "WHITE", "BLACK")).Select
to clear contents prior to adding data.
My question is can I select ALL sheets and then deselect "Main", "View", "Annual" and "Sick" which would do away with the necessity of always using the array of all staff names?
This would help when the workbooks have to be edited when someone leaves or starts with the company
Many thanks in advance
I have text information that i need to arrange in an issuable format. Unforunitaly the cells change when the data is pasted in.
For exampe if i have 2 parts of informatin being
Name: Tom
Surname: Jones
Is there any forumlas that let able you to search a column for in this instance "name:" and place the value of that cell into the forumla cell!
Example
Cel C1 = Name: Tom
Cell C2 Surname: Jones
Cell A1= Formula searches Column C for Name: Finds Cell C1 and Places "Name: Tom" in cell A1
Thanks in Advance
Hi,
Attach is the excel file in question. Here is what I would like to achieve:
Right now cell B7 is referencing cell D3. However I want this to be dynamic in the sense that suppose I change the Phase 2 (which starts from ww3 (cell D3)) to ww5 (cell F3) then I would like that B7 should also get updated to F3 instead of D3.
Basically the Development phase of "Support" Project starts only when Phase 2 of "Project 1" starts.
ww = Work Week.
In short cell B7 should always be in sync with the ww of Phase 2 of Project 1.
Thanks
Hi, i am new here. I have tried searching the net for examples to help me with a formula i am trying to create and i was led here. hope someone here can help me out.
what i am trying to do, is have a argument formula (i think) that return multiple options if various condtiond are met.
I have the first argument right, returning the correct answer without a problem, however i need to add to this.
EG: =IF(B3="","",IF(OR(B3=1,B3=11,B3=21,B3=31,B3=41) , "1,11,21,31", ""))
The above is set in cell D3 and works, so if the value of B3 is either 1,11,21,31 or 41, the result in D3 is 1,11,21,31.
Now what i need it to do aswell is, if B3 is any other value, D3's result needs to be different.
I have coded the rest of the formulas, but can not string it together into one argument for B3, always returns an error.
So the below arguments should follow the above:
=IF(B3="","",IF(OR(B3=2,B3=12,B3=22,B3=32,B3=42) , "2,12,22,32", ""))
=IF(B3="","",IF(OR(B3=3,B3=13,B3=23,B3=33,B3=43) , "3,13,23,33", ""))
=IF(B3="","",IF(OR(B3=4,B3=14,B3=24,B3=34,B3=44) , "4,14,24,34", ""))
=IF(B3="","",IF(OR(B3=5,B3=15,B3=25,B3=35,B3=45) , "5,15,25,35", ""))
=IF(B3="","",IF(OR(B3=6,B3=16,B3=26,B3=36,B3=46) , "6,16,26,36", ""))
=IF(B3="","",IF(OR(B3=7,B3=17,B3=27,B3=37,B3=47) , "7,17,27", ""))
=IF(B3="","",IF(OR(B3=8,B3=18,B3=28,B3=38,B3=48) , "8,18,28 ", ""))
=IF(B3="","",IF(OR(B3=9,B3=19,B3=29,B3=39,B3=49) , "9,19,29", ""))
=IF(B3="","",IF(OR(B3=0,B3=10,B3=20,B3=30,B3=40) , "0,10,20,30", ""))
In a nut shell, B3 has a value, a single cell argument needs to determine if the value in B3 is met and return a specific result in cell D3.
Any idea's......
Thanks in advance
I have the following code for a dde input everyhting is working fine except that, it is happening every second as the incoming DDE data is everrchanging and so the calculations always kick start off, every second.
This is making the program take lot of memory and computer resources is there any way to simplify this and yet make the calculations as precise as now? pls help
Code:
Private Sub Worksheet_Calculate()
'This is the code that decides wether to initiate the main program, _
by depending on the value typed in C30 in sheet Prima. _
Make sure that the value in C30 is not directly input but rather referenced to another cell this is in order _
to kick start the CALCULATE event in this case it is referenced to D30
If (Range("C30").Value = 1) Then
If (Range("C2").Value > Range("G2").Value) Then
Range("E2").Value = Range("C2").Value
Range("G2").Value = Range("C2").Value
Else
Range("E2").Value = Range("G2").Value
End If
If (Range("C2").Value < Range("H2").Value) Then
Range("F2").Value = Range("C2").Value
Range("H2").Value = Range("F2").Value
Else
Range("F2").Value = Range("H2").Value
End If
If (Range("C3").Value > Range("G3").Value) Then
Range("E3").Value = Range("C3").Value
Range("G3").Value = Range("C3").Value
Else
Range("E3").Value = Range("G3").Value
End If
If (Range("C3").Value < Range("H3").Value) Then
Range("F3").Value = Range("C3").Value
Range("H3").Value = Range("F3").Value
Else
Range("F3").Value = Range("H3").Value
End If
If (Range("C4").Value > Range("G4").Value) Then
Range("E4").Value = Range("C4").Value
Range("G4").Value = Range("C4").Value
Else
Range("E4").Value = Range("G4").Value
End If
If (Range("C4").Value < Range("H4").Value) Then
Range("F4").Value = Range("C4").Value
Range("H4").Value = Range("F4").Value
Else
Range("F4").Value = Range("H4").Value
End If
If (Range("C5").Value > Range("G5").Value) Then
Range("E5").Value = Range("C5").Value
Range("G5").Value = Range("C5").Value
Else
Range("E5").Value = Range("G5").Value
End If
If (Range("C5").Value < Range("H5").Value) Then
Range("F5").Value = Range("C5").Value
Range("H5").Value = Range("F5").Value
Else
Range("F5").Value = Range("H5").Value
End If
If (Range("C6").Value > Range("G6").Value) Then
Range("E6").Value = Range("C6").Value
Range("G6").Value = Range("C6").Value
Else
Range("E6").Value = Range("G6").Value
End If
If (Range("C6").Value < Range("H6").Value) Then
Range("F6").Value = Range("C6").Value
Range("H6").Value = Range("F6").Value
Else
Range("F6").Value = Range("H6").Value
End If
If (Range("C7").Value > Range("G7").Value) Then
Range("E7").Value = Range("C7").Value
Range("G7").Value = Range("C7").Value
Else
Range("E7").Value = Range("G7").Value
End If
If (Range("C7").Value < Range("H7").Value) Then
Range("F7").Value = Range("C7").Value
Range("H7").Value = Range("F7").Value
Else
Range("F7").Value = Range("H7").Value
End If
If (Range("C8").Value > Range("G8").Value) Then
Range("E8").Value = Range("C8").Value
Range("G8").Value = Range("C8").Value
Else
Range("E8").Value = Range("G8").Value
End If
If (Range("C8").Value < Range("H8").Value) Then
Range("F8").Value = Range("C8").Value
Range("H8").Value = Range("F8").Value
Else
Range("F8").Value = Range("H8").Value
End If
If (Range("C9").Value > Range("G9").Value) Then
Range("E9").Value = Range("C9").Value
Range("G9").Value = Range("C9").Value
Else
Range("E9").Value = Range("G9").Value
End If
If (Range("C9").Value < Range("H9").Value) Then
Range("F9").Value = Range("C9").Value
Range("H9").Value = Range("F9").Value
Else
Range("F9").Value = Range("H9").Value
End If
If (Range("C10").Value > Range("G10").Value) Then
Range("E10").Value = Range("C10").Value
Range("G10").Value = Range("C10").Value
Else
Range("E10").Value = Range("G10").Value
End If
If (Range("C10").Value < Range("H10").Value) Then
Range("F10").Value = Range("C10").Value
Range("H10").Value = Range("F10").Value
Else
Range("F10").Value = Range("H10").Value
End If
If (Range("C11").Value > Range("G11").Value) Then
Range("E11").Value = Range("C11").Value
Range("G11").Value = Range("C11").Value
Else
Range("E11").Value = Range("G11").Value
End If
If (Range("C11").Value < Range("H11").Value) Then
Range("F11").Value = Range("C11").Value
Range("H11").Value = Range("F11").Value
Else
Range("F11").Value = Range("H11").Value
End If
If (Range("C12").Value > Range("G12").Value) Then
Range("E12").Value = Range("C12").Value
Range("G12").Value = Range("C12").Value
Else
Range("E12").Value = Range("G12").Value
End If
If (Range("C12").Value < Range("H12").Value) Then
Range("F12").Value = Range("C12").Value
Range("H12").Value = Range("F12").Value
Else
Range("F12").Value = Range("H12").Value
End If
If (Range("C13").Value > Range("G13").Value) Then
Range("E13").Value = Range("C13").Value
Range("G13").Value = Range("C13").Value
Else
Range("E13").Value = Range("G13").Value
End If
If (Range("C13").Value < Range("H13").Value) Then
Range("F13").Value = Range("C13").Value
Range("H13").Value = Range("F13").Value
Else
Range("F13").Value = Range("H13").Value
End If
If (Range("C14").Value > Range("G14").Value) Then
Range("E14").Value = Range("C14").Value
Range("G14").Value = Range("C14").Value
Else
Range("E14").Value = Range("G14").Value
End If
If (Range("C14").Value < Range("H14").Value) Then
Range("F14").Value = Range("C14").Value
Range("H14").Value = Range("F14").Value
Else
Range("F14").Value = Range("H14").Value
End If
End If
End Sub
when using excel it starts randomly making up words in the cell such as
"one day and foot and in a room one day"
"and law ... "
has anyone else come accross this issue?
The computer is virus checked regularly.
Any help would be great, thank you.
excel version 2003

