Email:      Pass:    Pass?
Close Window   
TE
Free Excel Tips in Your Email!
Join Over 20,000 Happy Subscribers!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Displaying Cell Date With "&" In Another Cell

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi folks...

I'm trying to get excel to display a quote, plus a cell's text/value... the problem I am having is that the format is a date, and it ends up showing something like 37896 instead of 11/25/2008 (those are examples, not actuals)

Here's the code:
Code:

=IF(OR(D56="insufficient",E56="insufficient",F56="insufficient",G56="insufficient",H56="insufficient",I56="insufficient",J56="insufficient",K56="insufficient",L56="insufficient",M56="insufficient",N56="insufficient",O56="insufficient",P56="insufficient",Q56="insufficient",R56="insufficient",S56="insufficient",T56="insufficient",U56="insufficient",V56="insufficient",W56="insufficient",X56="insufficient",Y56="insufficient",Z56="insufficient",AA56="insufficient",AB56="insufficient",AC56="insufficient"),"Warning, Cash Flow will become unsustainable by "&A63,"")


A63 is an Hlookup that references which cell is the first to say "insufficient" and checks down a few lines for the date.

Thanks!

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics







Hi,

I hope someone can help a bit of a newby! I have a sheet of text values and want to return a "TRUE" if there is a "Poor" or "Insufficient" in any of three columns (with 100 rows). So my data looks like this:

Q1.2 Q2.2 Q2.6
Insufficient Sufficient N/a no restrictive interventions
Poor Insufficient Insufficient
Sufficient Sufficient Sufficient

And I would like a value at the end of each row to show "Contains Poor or Insufficient" / TRUE.

Thanks,
Kev B


=if(D2="Insufficient","Below","Na",if(D2="Poor","Below","Na",if(D2="Sufficient","Above","Na",if(D2=" Excellent","Above","Na")

I am not sure why the above does not work. all I want is the above/below for a small dataset with values running Poor, Insufficient (Below) and Excellent, Sufficient (Above). Any help much appreciated!


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,")","")




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 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'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


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





The formula i want to use is the following:

=SUM('KCR 1'!G24="Yes",'KCR 1'!G25="Yes",'KCR 1'!G26="Yes",'KCR 1'!G27="Yes",'KCR 1'!G28="Yes",'KCR 1'!G29="Yes",'KCR 2'!G24="Yes",'KCR 2'!G25="Yes",'KCR 2'!G26="Yes",'KCR 2'!G27="Yes",'KCR 2'!G28="Yes",'KCR 2'!G29="Yes",'KCR 3'!G24="Yes",'KCR 3'!G25="Yes",'KCR 3'!G26="Yes",'KCR 3'!G27="Yes",'KCR 3'!G28="Yes",'KCR 3'!G29="Yes",'KCR 4'!G24="Yes",'KCR 4'!G25="Yes",'KCR 4'!G26="Yes",'KCR 4'!G27="Yes",'KCR 4'!G28="Yes",'KCR 4'!G29="Yes",'KCR 5'!G24="Yes",'KCR 5'!G25="Yes",'KCR 5'!G26="Yes",'KCR 5'!G27="Yes",'KCR 5'!G28="Yes",'KCR 5'!G29="Yes",'KCR 6'!G24="Yes",'KCR 6'!G25="Yes",'KCR 6'!G26="Yes",'KCR 6'!G27="Yes",'KCR 6'!G28="Yes",'KCR 6'!G29="Yes",'KCR 7'!G24="Yes",'KCR 7'!G25="Yes",'KCR 7'!G26="Yes",'KCR 7'!G27="Yes",'KCR 7'!G28="Yes",'KCR 7'!G29="Yes",'KCR 8'!G24="Yes",'KCR 8'!G25="Yes",'KCR 8'!G26="Yes",'KCR 8'!G27="Yes",'KCR 8'!G28="Yes",'KCR 8'!G29="Yes",'KCR 9'!G24="Yes",'KCR 9'!G25="Yes",'KCR 9'!G26="Yes",'KCR 9'!G27="Yes",'KCR 9'!G28="Yes",'KCR 9'!G29="Yes",'KCR 10'!G24="Yes",'KCR 10'!G25="Yes",'KCR 10'!G26="Yes",'KCR 10'!G27="Yes",'KCR 10'!G28="Yes",'KCR 10'!G29="Yes")/60

Obviously very longwinded, so i guess im asking if theres a much shorter way of doing this calculation, or if anyone can recommend a completly different way altogether?!?

Many thanks!!!


Just curious if I can Say Fill this Combobox with letters from A to AZ, so I do not need to do this:

Code:

Me.cobColumn.List = Array("A", "B", "C", "D", "E", "F", "G", "H", _
        "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", _
        "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", _
        "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", _
        "AY", "AZ")





I've researched a few options but havent been able to make anything work.
Here is the formula I know would would if the limit were not 7....

=IF(C5="AT",K5*9.5%,IF(C5="Cr",K5*9.8%,IF(C5 ="Du",K5*9.8%,IF(C5="OH",K5*9.5%,IF(C5="PWC",K5 *9.5%,IF(C5="Sc",K5*9.8%,IF(C5="Sp",K5*9.8 %,IF(C5="To",K5*9.8%,IF(C5="Tr",K5*9.8%, IF(C5="UTV",K5*9.8%))))))))))

Well I've added a few more values now totaling 14...

I tried this and I get a "false" AND then the correct answer, obviously because the correct value is in the second IF statement.

=IF(C5="ATV S",J5*9.5%,IF(C5="ATV U",J5*9.5%,IF(C5="Cr",J5*9.8%,IF(C5="Cr T",J5*9.8%,IF(C5="DS",J5*9.8%,IF(C5="MX",J5*9.5%,IF(C5="Off", J5*9.5%)))))))&IF(C5="PWC",J5*9.5%,IF(C5="Scr" ,J5*9.8%,IF(C5="Sta",J5*9.8%,IF(C5="Sp",J5 *9.8%,IF(C5="Sp T",J5*9.8%,IF(C5="Tr",J5*9.5%,IF(C5="UTV",J5*9.5%)))))))

The I tried this, but I get "formula contains an error"

=LOOKUP(C5,{"ATV S","ATV U","Cruiser","Cr T","DS","MX","Off","PWC","Scr","St"," Sp","Sp T","Tr","UTV"},{J5*9.5%,J5*9.5%,J5*9.8% ,J5*9.8%,J5*9.8%,J5*9.5%,J5*9.5%,J5*9.5%,J5*9.8%,J 5*9.8%,J5*9.8%,J5*9.8%,J5*9.5%,J5*9.5%})

I'd REALLY appreciate any help you can give...I'm stumped.

-T


Hi,

I am having problems using IF in a formula.

This one works:

=
IF(C5="01","A",
IF(C5="02","B",
IF(C5="03","C",
IF(C5="04","D",
IF(C5="05","E",
IF(C5="06","F",
IF(C5="08","G",
IF(C5="09","H"
))))))))


This one does not work. I get an error message.

=
IF(C5="01","A",
IF(C5="02","B",
IF(C5="03","C",
IF(C5="04","D",
IF(C5="05","E",
IF(C5="06","F",
IF(C5="08","G",
IF(C5="09","H"
IF(C5="11","I",
IF(C5="12","J",
IF(C5="13","K",
IF(C5="14","L",
IF(C5="15","M",
IF(C5="16","N",
))))))))))))))


As you can see, I am just giving more values. Is there a limit for IF to be
used ?

Thanks in advance for your help



Simplifying multiple IF-Then while maintaining one macro.

How can i simplify this? This is only 2 of 5, idealy i want this to do exactly what it's doing, checking to see what value is in the cells B5:B9

There has to be an easier way.

Code:

Sub Players()
If Range("B5") = "1" Then
    Range("G16").Value = Range("G16").Value + 1
Else
   If Range("B5") = "2" Then
    Range("G17").Value = Range("G17").Value + 1
    Else
        If Range("B5") = "3" Then
            Range("G18").Value = Range("G18").Value + 1
        Else
            If Range("B5") = "4" Then
            Range("G19").Value = Range("G19").Value + 1
            Else
                If Range("B5") = "5" Then
                Range("G20").Value = Range("G20").Value + 1
                Else
                    If Range("B5") = "6" Then
                    Range("G21").Value = Range("G21").Value + 1
                    Else
                        If Range("B5") = "7" Then
                        Range("G22").Value = Range("G22").Value + 1
                        Else
                            If Range("B5") = "8" Then
                            Range("G23").Value = Range("G23").Value + 1
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End If
If Range("B6") = "1" Then
    Range("G16").Value = Range("G16").Value + 1
Else
   If Range("B6") = "2" Then
    Range("G17").Value = Range("G17").Value + 1
    Else
        If Range("B6") = "3" Then
            Range("G18").Value = Range("G18").Value + 1
        Else
            If Range("B6") = "4" Then
            Range("G19").Value = Range("G19").Value + 1
            Else
                If Range("B6") = "5" Then
                Range("G20").Value = Range("G20").Value + 1
                Else
                    If Range("B6") = "6" Then
                    Range("G21").Value = Range("G21").Value + 1
                    Else
                        If Range("B6") = "7" Then
                        Range("G22").Value = Range("G22").Value + 1
                        Else
                            If Range("B6") = "8" Then
                            Range("G23").Value = Range("G23").Value + 1
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End If
End Sub





I need help condensing this repeating code. Thanks.

Code:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/1/2007 by Janet
Sheets("H").Select
Range("c3").Select
ActiveCell.Resize(, 100) = Array("=data!f4", "=data!f5", "=data!f6", "=data!f7", "=data!f8", "=data!f9", "=data!f10", "=data!f11", "=data!f12", "=data!f13", _
                                "=data!f15", "=data!f16", "=data!f17", "=data!f18", "=data!f19", "=data!f20", "=data!f21", "=data!f22", "=data!f23", "=data!f24", _
                                "=data!f25", "=data!f26", "=data!f27", "=data!f28", "=data!f29", "=data!f30", "=data!f31", "=data!f32", "=data!f33", "=data!f34", _
                                "=data!f35", "=data!f36", "=data!f37", "=data!f38", "=data!f39", "=data!f40", "=data!f41", "=data!f42", "=data!f43", "=data!f44", _
                                "=data!f45", "=data!f46", "=data!f47", "=data!f48", "=data!f49", "=data!f50", "=data!f51", "=data!f52", "=data!f53", "=data!f54", _
                                "=data!f55", "=data!f56", "=data!f57", "=data!f58", "=data!f59", "=data!f60", "=data!f61", "=data!f62", "=data!f63", "=data!f64", _
                                "=data!f65", "=data!f66", "=data!f67", "=data!f68", "=data!f69", "=data!f70", "=data!f71", "=data!f72", "=data!f73", "=data!f74", _
                                "=data!f75", "=data!f76", "=data!f77", "=data!f78", "=data!f79", "=data!f80", "=data!f81", "=data!f82", "=data!f83", "=data!f84", _
                                "=data!f85", "=data!f86", "=data!f87", "=data!f88", "=data!f89", "=data!f90", "=data!f91", "=data!f92", "=data!f93", "=data!f94", _
                                "=data!f95", "=data!f96", "=data!f97", "=data!f98", "=data!f99", "=data!f100", "=data!f101", "=data!f102", "=data!f103", "=data!f104")
Sheets("L").Select
Range("c3").Select
ActiveCell.Resize(, 100) = Array("=data!g4", "=data!g5", "=data!g6", "=data!g7", "=data!g8", "=data!g9", "=data!g10", "=data!g11", "=data!g12", "=data!g13", _
                                "=data!g15", "=data!g16", "=data!g17", "=data!g18", "=data!g19", "=data!g20", "=data!g21", "=data!g22", "=data!g23", "=data!g24", _
                                "=data!g25", "=data!g26", "=data!g27", "=data!g28", "=data!g29", "=data!g30", "=data!g31", "=data!g32", "=data!g33", "=data!g34", _
                                "=data!g35", "=data!g36", "=data!g37", "=data!g38", "=data!g39", "=data!g40", "=data!g41", "=data!g42", "=data!g43", "=data!g44", _
                                "=data!g45", "=data!g46", "=data!g47", "=data!g48", "=data!g49", "=data!g50", "=data!g51", "=data!g52", "=data!g53", "=data!g54", _
                                "=data!g55", "=data!g56", "=data!g57", "=data!g58", "=data!g59", "=data!g60", "=data!g61", "=data!g62", "=data!g63", "=data!g64", _
                                "=data!g65", "=data!g66", "=data!g67", "=data!g68", "=data!g69", "=data!g70", "=data!g71", "=data!g72", "=data!g73", "=data!g74", _
                                "=data!g75", "=data!g76", "=data!g77", "=data!g78", "=data!g79", "=data!g80", "=data!g81", "=data!g82", "=data!g83", "=data!g84", _
                                "=data!g85", "=data!g86", "=data!g87", "=data!g88", "=data!g89", "=data!g90", "=data!g91", "=data!g92", "=data!g93", "=data!g94", _
                                "=data!g95", "=data!g96", "=data!g97", "=data!g98", "=data!g99", "=data!g100", "=data!g101", "=data!g102", "=data!g103", "=data!g104")
Sheets("C").Select
Range("c3").Select
ActiveCell.Resize(, 100) = Array("=data!d4", "=data!d5", "=data!d6", "=data!d7", "=data!d8", "=data!d9", "=data!d10", "=data!d11", "=data!d12", "=data!d13", _
                                "=data!d15", "=data!d16", "=data!d17", "=data!d18", "=data!d19", "=data!d20", "=data!d21", "=data!d22", "=data!d23", "=data!d24", _
                                "=data!d25", "=data!d26", "=data!d27", "=data!d28", "=data!d29", "=data!d30", "=data!d31", "=data!d32", "=data!d33", "=data!d34", _
                                "=data!d35", "=data!d36", "=data!d37", "=data!d38", "=data!d39", "=data!d40", "=data!d41", "=data!d42", "=data!d43", "=data!d44", _
                                "=data!d45", "=data!d46", "=data!d47", "=data!d48", "=data!d49", "=data!d50", "=data!d51", "=data!d52", "=data!d53", "=data!d54", _
                                "=data!d55", "=data!d56", "=data!d57", "=data!d58", "=data!d59", "=data!d60", "=data!d61", "=data!d62", "=data!d63", "=data!d64", _
                                "=data!d65", "=data!d66", "=data!d67", "=data!d68", "=data!d69", "=data!d70", "=data!d71", "=data!d72", "=data!d73", "=data!d74", _
                                "=data!d75", "=data!d76", "=data!d77", "=data!d78", "=data!d79", "=data!d80", "=data!d81", "=data!d82", "=data!d83", "=data!d84", _
                                "=data!d85", "=data!d86", "=data!d87", "=data!d88", "=data!d89", "=data!d90", "=data!d91", "=data!d92", "=data!d93", "=data!d94", _
                                "=data!d95", "=data!d96", "=data!d97", "=data!d98", "=data!d99", "=data!d100", "=data!d101", "=data!d102", "=data!d103", "=data!d104")
End Sub





I am trying to calculate averages of Key Stage 2 SAT results that have the following outcomes:

"5a","5b","5c","4a","4b","4c","3a","3b","3c","2a","2b","2c","1a","1b","1c"

Each student has three values like:

4c 4b 5b

I am after a formula that will give me an average of all three or more. I have got as far as:

(D4:F4,{"5a","5b","5c","4a","4b","4c","3a","3b","3c","2a","2b","2c","1a","1b","1c"},{15,14,13,12,10, 9,8,7,6,5,4,3,2,1})

This seems to be in the right area, but I cant quite get enough information to get the thing working. I know how to do it with VLOOKUP, but that requires a separate table and is then independent on that, this way it will be easy to share the formula with colleagues.

Help greatly appreciated on this one.


Hi Everyone

I was wondering if any could please tell me what I am doing wrong with the following, its giving me an error.

=IF(AND(R8="",P8="",N8="",L8="",I8=""),"",IF(AND(R8="",P8="",N8="",L8="",I8<>""),"Internal Review",IF(AND(R8="",P8="",N8="",L8<>"",I8<>""),"CfH Review",IF(AND(R8="",P8="",N8<>"",L8<>"",I8<>""),"CSC Update",IF(AND(R8="",P8<>"",N8<>"",L8<>"",I8<>""),"CfH Approval",IF(AND(R8<>"",P8<>"",N8<>"",L8<>"",I8<>""),"Complete",IF(AND(R8<>"",P8<>"",N8<>"",L8<>"",I 8=""),"Missing Date for Internal Review",IF(AND(R8<>"",P8<>"",N8<>"",L8="",I8<>""),"Missing Date for CfH Review Submission",IF(AND(R8<>"",P8<>"",N8="",L8<>"",I8<>""),"Missing Date for CfH Feedback",IF(AND(R8<>"",P8="",N8<>"",L8<>"",I8<>""),"Missing Date for Re-Issue to CfH",IF(AND(R8<>"",P8="",N8="",L8="",I8=""),"Missing Some Actual Dates",IF(AND(R8<>"",P8<>"",N8="",L8="",I8=""),"Missing Some Actual Dates"))))

I would be grateful if you could help.

(Ignore spaces in formula)

Many Thanks.


Hello all -

I'm trying to write a formula in a macro that will look at the value in J2 and based on that value, write out a new value in K2. Here's what I've got. It works until I add Camper/Trailer to the mix.

Anyone have any idea why this is erroring out on me?

Code:

ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]=""AUTOP"",""AUTO"",IF(RC[-1]=""Watercraft"",""BOAT"",IF(RC[-1]=""PHOME"",""HOME"",IF(RC[-1]=""DFIRE"",""DWF"",IF(RC[-1]=""EQ"",""EQU"",IF(RC[-1]=""PUMBR"",""UMB"",IF(RC[-1]=""Camper/Trailer"",""TRAV"",IF(RC[-1]=""Motorcycle"",""CYCL"",IF(RC[-1]=""INMRP"",""INLM"",RC[-1])))))))))"





Dear guru........

i have export some data (contacts) from my mobile in excel in the following format

SURESH"""""""""""'',""""""""""9852653563""""""""""suresh@ril.com""""""""""""""
PRADEEP GOSWAMI""""""""",""""""""02836226358""""""""""""umamarine@yahoo.com

total 1000 contacts in this format now i want to split it them with suitable format
pls guide for it.
I tried for Text to column but the exported data is not in the sequence.
regds
devesh


hi,

i want to associate a lookup function to a specific cell through command button submit like this

.sheets("Data").Range("H4").Formula = AQL

where

AQL = "=LOOKUP(G4"+ " ;{""A"";""B"";""C"";""D"";""E"";""F"";""G"";""H"";""J"";""K"";""L"";""M"";""N"";""P"";""Q""};{0;0;0; 0;0;0;0;1;1;2;3;5;7;10;14})"


but an error occur when i run the macro

Rui Azenha


Please, help me, guys.

I'm trying to introduce this formula:

=LOOKUP(B10,{20,20.25,20.5,20.75,21,21.25,21.5,21.75,22,22.25,22.5,22.75,23,23.25,23.5,23.75,24,24.2 5,24.5,24.75,25,25.25,25.5,25.75,26,26.25,26.5,26.75,27,27.25,27.5,27.75,28,28.25,28.5,28.75,29,29.2 5,29.5,29.75,30,30.25,30.5,30.75,31,31.25,31.5,31.75,32,32.25,32.5,32.75,33,33.25,33.5,33.75,34,34.2 5,34.5,34.75,35,35.25,35.5,35.75,36,36.25,36.5,36.75,37,37.25,37.5,37.75,38,38.25,38.5,38.75,39,39.2 5,39.5,39.75,40,40.25,40.5,40.75,41,41.25,41.5,41.75,42,42.25,42.5,42.75,43},{"8 5/8","8 3/4","8 7/8","9","9 1/8","9 1/4","9 3/8","9 1/2","9 5/8","9 3/4","9 7/8","10","10 1/8","10 1/4","10 3/8","10 1/2","10 5/8","10 3/4","10 7/8","11","11 1/8","11 1/4","11 3/8","11 1/2","11 5/8","11 3/4","11 7/8","12","12 1/8","12 1/4","12 3/8","12 1/2","12 5/8","12 3/4","12 7/8","13","13 1/8","13 1/4","13 3/8","13 1/2","13 5/8","13 3/4","13 7/8","14","14 1/8","14 1/4","14 3/8","14 1/2","14 5/8","14 3/4","14 7/8","15","15 1/8","15 1/4","15 3/8","15 1/2","15 5/8","15 3/4","15 7/8","16","16 1/8","16 1/4","16 3/8","16 1/2","16 5/8"}) etc etc

All I need is to insert more data in this formula. But... it's too long. Any sugestion?


I want to copy over text from various fields into a single field.
Here is the only solution (my limited time and my limited programing savy) I
have found so far. Is there a way to minimise or shorten this
equation/formula:

=A3&" "&A4&" "&A5&" "&A6&" "&A7&" "&A8&" "&A9&" "&A10&" "&A11&" "&A12&"
"&A13&" "&A14&" "&A15&" "&A16&" "&A17&" "&A18&" "&A19&" "&A20&" "&A21&"
"&A22&" "&A23&" "&A24&" "&A25&" "&A26&" "&A27

In essense, I want to tell a cell to capture the info in a3 through a27 with
a space inbetween data/value returned.

Thanks




The numbers in B3=0, C3=2 and D3=0 the formula I'm using is:

=IF(B3="0",1,IF(B3="5",1,IF(B3="1",2,IF(B3="6",2,IF(B3="2",3,IF(B3="7",3,IF(B3="3",4,IF(B3="8",4,IF( B3="4",5,IF(B3="9",5))))))))))

For cell "B3" the formula works

Now for both the next two cells C3 & D3 the formula is:

=IF(C3="0",1,IF(C3="5",1,IF(C3="1",2,IF(C3="6",2,IF(C3="2",3,IF(C3="7",3,IF(C3="3",4,IF(C3="8",4,IF( C3="4",5,IF(C3="9",5))))))))))

=IF(D3="0",1,IF(D3="5",1,IF(D3="1",2,IF(D3="6",2,IF(D3="2",3,IF(D3="7",3,IF(D3="3",4,IF(D3="8",4,IF( D3="4",5,IF(D3="9",5))))))))))

Doesn't work and displays "FALSE" ?????

I don't the luxury of wasting time, the formula should work but I don't understand why it isn't, could anyone please help me. (Probably just stressed and tried.)

Thanks ahead of time,


Hi --

I tried to follow examples, but I keep getting errors, what am I doing wrong. I want to check a series of columns and if there is a "1" in one of columns I want it to write CMS in a blank cell in the same row. (basically I am trying to get all the "1's" in one column).

This is what I have so far:

=IF(A59<>1,"","CMS",IF(B59<>1,"","CMS",IF(C59<>1,"","CMS",IF(D59<>1,"","CMS",IF(E59<>1,"","CMS"))))

I also tried this:

=IF(A59<>1,"","CMS",IF(B59<>1,"","CMS",IF(C59<>1,"","CMS",IF(D59<>1,"","CMS",IF(E59<>1,"","CMS",IF(F 59<>1,"","CMS"))))))

What am I doing wrong?

Thanks. Stephanie


I am unable understand the following code. Please explain me about this chain formula and explain about the purpose these many quatation marks.

str = Sheets("Sample1").Range("B" & i).Value
Sheets("Sample3").Range("C" & i).Value = "=IF(ISERROR(FIND(" & """-""" & "," & """" & _
str & """" & ",FIND(" & """-""" & "," & """" & str & """" & ",FIND(" & """-""" & "," & """" & _
str & """" & ",1)+1)+1)) = TRUE,"""", MID(" & """" & str & """" & ",(FIND(" & """-""" & "," & """" & _
str & """" & " ,FIND(" & """-""" & "," & """" & str & """" & ",1)+1))+1,1))"

Thanks&Regards
Ramesh


Please forgive me I am not a programer but I receive emails which copy CSV file data. Is there a way of importing the data seperated by commas into an excel spreadsheet.

The data seperated by comas matches the excel file exactly.
RAW DATA BELOW:
"22","CCC","FF44","222","12345678","12345678903341","","XXX","1","K","12.50","CLOTHING ETC ","","BA","","","","BOB STORE","KING RD","NEW YORK","","","US","","BOB STORE"," NORTH TOWER","NEW YORK","","22211","US","","A","21","l","","","","","","","","","","","","","","","86","","GB","1111", "USD","","Y

THANKS


Please forgive me I am not a programer but I receive emails which copy CSV file data. Is there a way of importing the data seperated by commas into an excel spreadsheet.

The data seperated by comas matches the excel file exactly.
RAW DATA BELOW:
"22","CCC","FF44","222","12345678","12345678903341","","XXX","1","K","12.50","CLOTHING ETC ","","BA","","","","BOB STORE","KING RD","NEW YORK","","","US","","BOB STORE"," NORTH TOWER","NEW YORK","","22211","US","","A","21","l","","","","","","","","","","","","","","","86","","GB","1111", "USD","","Y

THANKS