Displaying Cell Date With "&" In Another Cell 


Displaying Cell Date With "&" In Another Cell  Excel 
View Answers 
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:
A63 is an Hlookup that references which cell is the first to say "insufficient" and checks down a few lines for the date.
Thanks!
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!
Similar Excel Video Tutorials
Day Numbers to Dates
 See how to take the number 1 (for day one of the month) and use the DATE function to convert it to a serial number formatted as a date. =DATE(2008,11, ...
Conditional Format Based On Diff Cell
 See how to create conditional formatting based on a different cell. See how to highlight (add formatting to a cell) a number based on a date in a diff ...
Create Sequential Dates Across Sheets & Extract Sheet Name To Cell
 Download file: http://people.highline.edu/mgirvin/
Given that that each sheet summarizes daily data and each sheet is names 1, 2, 3, 4 etc., See ...
Given that that each sheet summarizes daily data and each sheet is names 1, 2, 3, 4 etc., See ...
Keyboard Shortcut For Date And Time In Same Cell
 Dave "Excel Master" from Florida sends in this cool trick for adding todays date and time to a cell with a keyboard shortcut. ...
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
 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 Popup Windows in Excel
 This is a very simple Message Box, popup window, macro for Excel that illustrates how to put new lines, the same thi
 This is a very simple Message Box, popup 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
 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
 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
 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
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 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,")","")
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?
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...
=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
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:
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!!!
=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:
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
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
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 IFThen 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:
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:
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.
"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 ReIssue 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.
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 ReIssue 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:
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
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
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'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
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,
=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 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
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
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
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