More Than 7 Ifs Nested? 


More Than 7 Ifs Nested?  Excel 
View Answers 
Hi All,
I have a formula with 7 IFs Nested within an IF formula. I also have some AND functions nested within it. I need to add more IFs, is this possible? Here is a copy of the current working formula:
=IF(AND(GST="",K28<>""),"Please enter",IF(AND(GST<>"",K28<>"",F28=""),"< Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28=""),"< Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28<>"",E28="Enter Date Above"),"< Enter ",IF(K28="SP",220,IF(K28="TPR",109.09,IF(K28="TD",136.36,IF(K28="TA",100,""))))))))
I just want to add another IF at the end like this:
=IF(AND(GST="",K28<>""),"Please enter",IF(AND(GST<>"",K28<>"",F28=""),"< Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28=""),"< Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28<>"",E28="Enter Date Above"),"< Enter ",IF(K28="SP",220,IF(K28="TPR",109.09,IF(K28="TD",136.36,IF(K28="TA",100,IF(K28="TA",100,""))))) ))))
But this comes up with an error, I assume that there must be a maximum number of items to nest?
Is there away around this?
This formula is what I have created inside an invoice that I get my staff to use. The idea is that it is Idiot proof, ie if they forget to enter whether or not they are registered for GST, then it will tell them. If one of the items on the form are missing, it will tell them, but then the last couple are for their pay.
Make any sense? think I have just confused myself... lol
Any help would be appreciated.
Clint
I have a formula with 7 IFs Nested within an IF formula. I also have some AND functions nested within it. I need to add more IFs, is this possible? Here is a copy of the current working formula:
=IF(AND(GST="",K28<>""),"Please enter",IF(AND(GST<>"",K28<>"",F28=""),"< Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28=""),"< Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28<>"",E28="Enter Date Above"),"< Enter ",IF(K28="SP",220,IF(K28="TPR",109.09,IF(K28="TD",136.36,IF(K28="TA",100,""))))))))
I just want to add another IF at the end like this:
=IF(AND(GST="",K28<>""),"Please enter",IF(AND(GST<>"",K28<>"",F28=""),"< Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28=""),"< Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28<>"",E28="Enter Date Above"),"< Enter ",IF(K28="SP",220,IF(K28="TPR",109.09,IF(K28="TD",136.36,IF(K28="TA",100,IF(K28="TA",100,""))))) ))))
But this comes up with an error, I assume that there must be a maximum number of items to nest?
Is there away around this?
This formula is what I have created inside an invoice that I get my staff to use. The idea is that it is Idiot proof, ie if they forget to enter whether or not they are registered for GST, then it will tell them. If one of the items on the form are missing, it will tell them, but then the last couple are for their pay.
Make any sense? think I have just confused myself... lol
Any help would be appreciated.
Clint
Similar Excel Video Tutorials
More than 7 Nested IF functions
 See what to do if you must have more than 7 nested IF functions: 1) Use the Ampersand, Use different cells, buy Excel 2007, use VLOOKUP, or Use a Bool ...
AGGREGATE function Ignores Nested SUBTOTALs for Calculations
 See how to make a calculation on a column of values that contain Subtotals that you do not want to include in the calculations. The AGGREGATE function ...
Nested IF & Short Term Loans
 Nested IF example. See how to use the TEXT function, concatenation using the Ampersand (&) symbol and the IF function to create labels and loan ca ...
Nested IF function Convert From Actual Days to Billed Days
 See how to use the IF function and Date Math to Convert From Actual Days to Billed Days for rented items. Learn about Nesting IF functions and the log ...
Helpful Excel Macros
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
Hide Formulas in a Worksheet and Prevent Deletion
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Enter Text/Characters with a DoubleClick of the Mouse
 This macro allows you to enter any text or number, that you specify, in a cell on which you doubleclick. In the exampl
 This macro allows you to enter any text or number, that you specify, in a cell on which you doubleclick. In the exampl
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel  AutoFilter
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Open any Program from Excel
 This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie
 This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie
Similar Topics
I have this huge formula that I am using to create a field with text in it defining ingredients in food items. Is there anyway I can break it up?
Here's the formula:
Quote:
=C2&IF(SUM(D2:E2)>0," ","")
&IF(D2>0,"Vegetarian","")
&IF(D2=3," on request","")
&IF(SUM(D2:E2)=2," or ","")
&IF(E2>0,"Vegan","")
&IF(E2=3," on request","")
&IF(SUM(D2:E2)=2," on request","")
&IF(SUM(F2:O2)>0," contains (","")
&IF(P2=1," May Contain","")
&IF(F2>0,"Alcohol","")
&IF(AND(G2=1,H2=0,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(Q2=1," May Contain","")
&IF(G2>0," Dairy","")
&IF(AND(H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(R2=1," May Contain","")
&IF(H2>0," Eggs","")
&IF(AND(I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(S2=1," May Contain","")
&IF(I2>0," Gluten","")
&IF(AND(J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(T2=1," May Contain","")
&IF(J2>0," Soy","")
&IF(AND(K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(U2=1," May Contain","")
&IF(K2>0," Peanuts","")
&IF(AND(L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(V2=1," May Contain","")
&IF(L2>0," Nuts","")
&IF(AND(M2=1,N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(W2=1," May Contain","")
&IF(M2>0," Seeds","")
&IF(AND(N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(X2=1," May Contain","")
&IF(N2>0," Fish","")
&IF(AND(O2=1,SUM(F2:O2)>1)," &","")
&IF(Y2=1," May Contain","")
&IF(O2>0," Shell Fish","")
&IF(SUM(F2:O2)>0,")","")
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,")","")
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
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?
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
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!!!
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
Here is a clip of my table"
"a" "b" "c"
"100104" "004" "304"
"105108" "005" "305"
"109113" "004" "304"
"114115" "005" "305"
"116" "004" "304"
"117119" "005" "305"
"120123" "004" "304"
"124126" "005" "305"
I would like to have an index function search the first column to find what row it corresponds to. I would like to be able to enter "106" and have it reference the row "105108".
How could I do that?
Bonus help: These are actually the first three numbers of ZIP codes. I would idealls like to have a function where you could input the whole 5 digit zip and it would determine the row. ie. enter "10633" and have it reference the "105108" row.
Thanks for your help!
"a" "b" "c"
"100104" "004" "304"
"105108" "005" "305"
"109113" "004" "304"
"114115" "005" "305"
"116" "004" "304"
"117119" "005" "305"
"120123" "004" "304"
"124126" "005" "305"
I would like to have an index function search the first column to find what row it corresponds to. I would like to be able to enter "106" and have it reference the row "105108".
How could I do that?
Bonus help: These are actually the first three numbers of ZIP codes. I would idealls like to have a function where you could input the whole 5 digit zip and it would determine the row. ie. enter "10633" and have it reference the "105108" row.
Thanks for your help!
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
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.
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")
Example Excel: Example.xlsx.
Below is the function I am using that is not working properly.{ =IF(A11=A10,"",IF(A11=A12=A13=A14=A15,C11&""&D11&""&E11&"("&F11&""&G11&")"&";"&C12&""&D12&""&E12&"("&F12&""&G12&")"&";"&C13&""&D13&""&E13&"("&F13&""&G13&")"&";"&C14&""&D14&""&E14&"("&F14&""&G14&")"&" "&C15&""&D15&""&E15&"("&F15&""&G15&")",IF(A11=A12=A13=A14,C11&""&D11&""&E11&"("&F11&""&G11&")"&";"&C12&""&D12&""&E12&"("&F12&""&G12&")"&")"&";"&C13&""&D13&""&E13&"("&F13&""&G13&")"&" "&C14&""&D14&""&E14&"("&F14&""&G14&")",IF(A11=A12=A13,C11&""&D11&""&E11&"("&F11&""&G11&")"&";"&C12&""&D12&""&E12&"("&F12&""&G12&")"&";"&C13&""&D13&""&E13&"("&F13&""&G13&")"&,IF(A11=A12,C11&""&D11&""&E11&"("&F11&""&G11&")"&";"&C12&""&D12&""&E12&"("&F12&""&G12&")"&,C11&""&D11&""&E11&"("&F11&""&G11&")"))))) }
The function works in most ways except for one, it does not combine more than a few pieces of information. I tried using the =CONCATENATE() function instead of using [&] but had the same result. If I pull out the function that combines data that works properly:
{ C11&""&D11&""&E11&"("&F11&""&G11&")"&";"&C12&""&D12&""&E12&"("&F12&""&G12&")"&";"&C13&""&D13&""&E13&"("&F13&""&G13&")"&";"&C14&""&D14&""&E14&"("&F14&""&G14&")"&" "&C15&""&D15&""&E15&"("&F15&""&G15&")}
Is there an alternative method to solve this problem? Is there someone to change a setting so this actually works?
I have a set of data with an identification code, I need to reduce the data down so there is only one identification code per the rest of the corresponding data. My function checks if there are multiple entries with the same identification code and combines the corresponding data into one cell.
I believe excel is incapable of handling the function.
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
Hi,
I need to create a function that can cope with more than 7 if ands, where it
can read what's in cell A1 and what's in cell B1 then spit out a defined
value in C1. For example,
If A1="a" and B1="a", then 1, but if A1="a" and B1="b", then 2 and so on for
combinations aa, ab, ac, ad, ae, ba, bb, bc..........ea, eb, ec, ed and ee.
My formula below works but only gets me up to bc.
=IF(AND(A1="a",B1="a"),"1",IF(AND(A1="a",B1="b"),"2",IF(AND(A1="a",B1="c"),"3",IF(AND(A1="a",B1="d") ,"3",IF(AND(A1="b",B1="a"),"2",IF(AND(A1="b",B1="b"),"2",IF(AND(A1="b",B1="c"),"3",)))))))
I've found some tricks to get around the 7limit IF functions but can't seem
to make them work for my nest IF(AND) function.
I hope this makes sense. Any help would be most appreciated.
thanks.
Rohan.
I need to create a function that can cope with more than 7 if ands, where it
can read what's in cell A1 and what's in cell B1 then spit out a defined
value in C1. For example,
If A1="a" and B1="a", then 1, but if A1="a" and B1="b", then 2 and so on for
combinations aa, ab, ac, ad, ae, ba, bb, bc..........ea, eb, ec, ed and ee.
My formula below works but only gets me up to bc.
=IF(AND(A1="a",B1="a"),"1",IF(AND(A1="a",B1="b"),"2",IF(AND(A1="a",B1="c"),"3",IF(AND(A1="a",B1="d") ,"3",IF(AND(A1="b",B1="a"),"2",IF(AND(A1="b",B1="b"),"2",IF(AND(A1="b",B1="c"),"3",)))))))
I've found some tricks to get around the 7limit IF functions but can't seem
to make them work for my nest IF(AND) function.
I hope this makes sense. Any help would be most appreciated.
thanks.
Rohan.
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.
)="Robert","Green",IF((B$10)="Nerida","Pink",IF((B$10)="Jack","Black",IF((B$10)="Max","Red",IF((B$10)="Jim","Blue",IF((B$10)="Chris","Orange",IF((B$10)="Sarah","Yellow",IF((B$10)="Rose","Purple",""))))))))
I need to add: IF((B$10)="Wade","Aqua" and IF((B$10)="Steve","White" yet.
How can I do this.
I am using the following formual but the results will not populate in currency format is there a quick solution to correct this? What am I missing?
=IF(OR(Y4="6to12"),"$170.00",IF(OR(Y4="12to18"),"$210.00",IF(OR(Y4="18to24"),"$315.00",IF(OR(Y4="24to30"),"$315.00",IF(OR(Y4="30to36"),"$690.00",IF(OR(Y4="36to42"),"$790.00",IF(OR(Y4="42to48"),"$950.00",IF(OR(Y4="48to54"),"$1200.00",IF(OR(Y4="grtr54"),"$1500.00",IF(OR(Y4="6to1248HR"),"$225.00",IF(OR(Y4="12to1848HR"),"$375.00",IF(OR(Y4="18to2448HR"),"$585.00",IF(OR(Y4="24to3048HR"),"$700.00",IF(OR(Y4="30to3648HR"),"$950.00",IF(OR(Y4="36to4248HR"),"$1250.00",IF(OR(Y4="42to4848HR"),"$1500.00",IF(OR(Y4="48to5448HR"),"$1900.00",IF(OR(Y4="grtr5448HR"),"$2500.00"))))))))))))))))))
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 all,
1) the code is starting from Range("A1").
i want it should run it on any cell of the sheet.
2) the value is also set for sr. no 1 to 20.
i want it should ask me like "how many items in table?"
3) the same code runs only one time in sheet.
i want it should runs for n times, means it should ask me like "how many tables?"
thanks in advance.
following is the code:
Sub Evaluation()
'
Range("A3").FormulaR1C1 = "Sr"
Range("A4").FormulaR1C1 = "No"
Range("A5").FormulaR1C1 = "1"
Range("A6").FormulaR1C1 = "2"
Range("A7").FormulaR1C1 = "3"
Range("A8").FormulaR1C1 = "4"
Range("A9").FormulaR1C1 = "5"
Range("A10").FormulaR1C1 = "6"
Range("A11").FormulaR1C1 = "7"
Range("A12").FormulaR1C1 = "8"
Range("A13").FormulaR1C1 = "9"
Range("A14").FormulaR1C1 = "10"
Range("A15").FormulaR1C1 = "11"
Range("A16").FormulaR1C1 = "12"
Range("A17").FormulaR1C1 = "13"
Range("A18").FormulaR1C1 = "14"
Range("A19").FormulaR1C1 = "15"
Range("A20").FormulaR1C1 = "16"
Range("A21").FormulaR1C1 = "17"
Range("A22").FormulaR1C1 = "18"
Range("A23").FormulaR1C1 = "19"
Range("A24").FormulaR1C1 = "20"
Range("B1").FormulaR1C1 = "Value1 "
Range("B3").FormulaR1C1 = "Name"
Range("B28").FormulaR1C1 = "Value3"
Range("C28").FormulaR1C1 = "=IF(ISERROR(R[3]C/RC[8]*100),"""",R[3]C/RC[8]*100)"
Range("D5:D24").FormulaR1C1 = "=IF(ISERROR(RC[1]/RC[1]),"""",RC[1]/RC[1])"
Range("D28").FormulaR1C1 = "Value8"
Range("E4").FormulaR1C1 = "Value9"
Range("G4").FormulaR1C1 = "Value13"
Range("G5:G24").FormulaR1C1 = "=IF(ISERROR(RC[2]*RC[1]),"""",RC[2]*RC[1])"
Range("H4").FormulaR1C1 = "Value14"
Range("H5:H24").FormulaR1C1 = _
"=IF(ISERROR(RC[2]*RC[4]/100),"""",RC[2]*RC[4]/100)"
Range("I3").FormulaR1C1 = "Value15"
Range("I4").FormulaR1C1 = "Value16"
Range("I5:I24").FormulaR1C1 = "=IF(ISERROR(RC[6]RC[2]),"""",RC[6]RC[2])"
Range("I28").FormulaR1C1 = "Value17"
Range("J4").FormulaR1C1 = "Value18"
Range("J5:J24").FormulaR1C1 = "=IF(ISERROR(RC[6]RC[2]),"""",RC[6]RC[2])"
Range("K3").FormulaR1C1 = "Value19"
Range("K4").FormulaR1C1 = "Value20"
Range("K5").FormulaR1C1 = _
"=IF(ISERROR(RC[8]/R[20]C[8]),"""",RC[8]/R[20]C[8])"
Range("K6").FormulaR1C1 = _
"=IF(ISERROR(R[1]C*RC[2]/R[1]C[8]),"""",R[1]C*RC[2]/R[1]C[8])"
Range("K7").FormulaR1C1 = _
"=IF(ISERROR(R[2]C*RC[2]/R[2]C[8]),"""",R[2]C*RC[2]/R[2]C[8])"
Range("K8").FormulaR1C1 = _
"=IF(ISERROR(R[3]C*RC[2]/R[3]C[8]),"""",R[3]C*RC[2]/R[3]C[8])"
Range("K9").FormulaR1C1 = _
"=IF(ISERROR(R[4]C*RC[2]/R[4]C[8]),"""",R[4]C*RC[2]/R[4]C[8])"
Range("K10").FormulaR1C1 = _
"=IF(ISERROR(R[5]C*RC[2]/R[5]C[8]),"""",R[5]C*RC[2]/R[5]C[8])"
Range("K11").FormulaR1C1 = _
"=IF(ISERROR(R[6]C*RC[2]/R[6]C[8]),"""",R[6]C*RC[2]/R[6]C[8])"
Range("K12").FormulaR1C1 = _
"=IF(ISERROR(R[7]C*RC[2]/R[7]C[8]),"""",R[7]C*RC[2]/R[7]C[8])"
Range("K13").FormulaR1C1 = _
"=IF(ISERROR(R[8]C*RC[2]/R[8]C[8]),"""",R[8]C*RC[2]/R[8]C[8])"
Range("K14").FormulaR1C1 = _
"=IF(ISERROR(R[9]C*RC[2]/R[9]C[8]),"""",R[9]C*RC[2]/R[9]C[8])"
Range("K15").FormulaR1C1 = _
"=IF(ISERROR(R[10]C*RC[2]/R[10]C[8]),"""",R[10]C*RC[2]/R[10]C[8])"
Range("K16").FormulaR1C1 = _
"=IF(ISERROR(R[11]C*RC[2]/R[11]C[8]),"""",R[11]C*RC[2]/R[11]C[8])"
Range("K17").FormulaR1C1 = _
"=IF(ISERROR(R[12]C*RC[2]/R[12]C[8]),"""",R[12]C*RC[2]/R[12]C[8])"
Range("K18").FormulaR1C1 = _
"=IF(ISERROR(R[13]C*RC[2]/R[13]C[8]),"""",R[13]C*RC[2]/R[13]C[8])"
Range("K19").FormulaR1C1 = _
"=IF(ISERROR(R[14]C*RC[2]/R[14]C[8]),"""",R[14]C*RC[2]/R[14]C[8])"
Range("K20").FormulaR1C1 = _
"=IF(ISERROR(R[15]C*RC[2]/R[15]C[8]),"""",R[15]C*RC[2]/R[15]C[8])"
Range("K21").FormulaR1C1 = _
"=IF(ISERROR(R[16]C*RC[2]/R[16]C[8]),"""",R[16]C*RC[2]/R[16]C[8])"
Range("K22").FormulaR1C1 = _
"=IF(ISERROR(R[17]C*RC[2]/R[17]C[8]),"""",R[17]C*RC[2]/R[17]C[8])"
Range("K23").FormulaR1C1 = _
"=IF(ISERROR(R[18]C*RC[2]/R[18]C[8]),"""",R[18]C*RC[2]/R[18]C[8])"
Range("K24").FormulaR1C1 = _
"=IF(ISERROR(R[19]C*RC[2]/R[19]C[8]),"""",R[19]C*RC[2]/R[19]C[8])"
Range("K25").FormulaR1C1 = _
"=IF(ISERROR(RC[8]*R[20]C/R[20]C[8]),"""",RC[8]*R[20]C/R[20]C[8])"
Range("K28").FormulaR1C1 = _
"=IF(ISERROR(RC[3]*R[23]C[8]/RC[6]),"""",RC[3]*R[23]C[8]/RC[6])"
Range("L3").FormulaR1C1 = "Value21"
Range("L4").FormulaR1C1 = "Value28"
Range("L5").FormulaR1C1 = "=IF(ISERROR(RC[1]/RC[1]),"""",RC[1]/RC[1])"
Range("L6").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[1]C[1]),"""",RC[1]/R[1]C[1])"
Range("L7").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[2]C[1]),"""",RC[1]/R[2]C[1])"
Range("L8").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[3]C[1]),"""",RC[1]/R[3]C[1])"
Range("L9").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[4]C[1]),"""",RC[1]/R[4]C[1])"
Range("L10").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[5]C[1]),"""",RC[1]/R[5]C[1])"
Range("L11").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[6]C[1]),"""",RC[1]/R[6]C[1])"
Range("L12").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[7]C[1]),"""",RC[1]/R[7]C[1])"
Range("L13").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[8]C[1]),"""",RC[1]/R[8]C[1])"
Range("L14").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[9]C[1]),"""",RC[1]/R[9]C[1])"
Range("L15").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[10]C[1]),"""",RC[1]/R[10]C[1])"
Range("L16").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[11]C[1]),"""",RC[1]/R[11]C[1])"
Range("L17").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[12]C[1]),"""",RC[1]/R[12]C[1])"
Range("L18").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[13]C[1]),"""",RC[1]/R[13]C[1])"
Range("L19").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[14]C[1]),"""",RC[1]/R[14]C[1])"
Range("L20").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[15]C[1]),"""",RC[1]/R[15]C[1])"
Range("L21").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[16]C[1]),"""",RC[1]/R[16]C[1])"
Range("L22").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[17]C[1]),"""",RC[1]/R[17]C[1])"
Range("L23").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[18]C[1]),"""",RC[1]/R[18]C[1])"
Range("L24").FormulaR1C1 = _
"=IF(ISERROR(RC[1]/R[19]C[1]),"""",RC[1]/R[19]C[1])"
Range("M3").FormulaR1C1 = "Value22"
Range("N4").FormulaR1C1 = "Value27"
Range("N5:N24").FormulaR1C1 = "=IF(ISERROR(RC[1]*RC[3]),"""",RC[1]*RC[3])"
Range("N25").FormulaR1C1 = _
"=IF(ISERROR(SUM(R[20]C:R[1]C)),"""",SUM(R[20]C:R[1]C))"
Range("N28").FormulaR1C1 = _
"=IF(ISERROR(R[3]C[11]/R[23]C[11]),"""",R[3]C[11]/R[23]C[11])"
End Sub
Hey folks,
I am kind of a newb to Excel. I have come up with this formula that works (I was really supprised when I hit enter and it actually worked) but I was wondering if theres any way to simplify it..
Code:
Basically it checks E3 to make sure something is there AND that E4 through E27 are blank... if those two conditions are met then D3 becomes the output... otherwise and error (nothing follows) is given.
Is there som way to shorten this for the blank cells? something like ..
Code:
I have tried doing that but it just says NAME? in the cell when I hit enter... is this the right format or is there an easier way to do what I want?
Thanks,
LF
I am kind of a newb to Excel. I have come up with this formula that works (I was really supprised when I hit enter and it actually worked) but I was wondering if theres any way to simplify it..
Code:
= IF(AND(E3<>"",E4="",E5="",E6="",E7="",E8="",E9="",E10="",E11="",E12="",E13="",E14="",E15="",E16="",E17="",E18="",E19="",E20="",E21="",E22="",E23="",E24="",E25="",E26="",E27=""),D3,"NOTHING FOLLOWS")
Basically it checks E3 to make sure something is there AND that E4 through E27 are blank... if those two conditions are met then D3 becomes the output... otherwise and error (nothing follows) is given.
Is there som way to shorten this for the blank cells? something like ..
Code:
=IF(AND(E3<>"",E4:E27=""),D3,"NOTHING FOLLOWS")
I have tried doing that but it just says NAME? in the cell when I hit enter... is this the right format or is there an easier way to do what I want?
Thanks,
LF
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
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?
Hi,
Using Excel 2002, SP3 on a Windows XP Machine.
I am trying to do a fairly simple thing that required a huge formula.
I have "labels" in column C3, on another worksheet (called 'codes') I have a list of new 'codes' for the things in C3. Each value in C3 can be one of 50. All 50 are represented in the 'codes'. There are approximately 1000 cells in C3. I would rather not do this by hand since the codes are things like "000000000000000000000000000100000000000"
This is my current formula:
=IF((C3="CCA01"),codes!B1,IF((C3="CCA02"),codes!B2,IF((C3="CCA03"),codes!B3,IF((C3="CCA04"),codes!B4,IF((C3="CCA05"),codes!B5,IF((C3="CCA06"),codes!B6,IF((C3="CCA07"),codes!B7,IF((C3="CCA08"),codes!B8,IF((C3="CCA09"),codes!B9,IF((C3="CCA10"),codes!B10,IF((C3="CCA11"),codes!B11,IF((C3="CCA12"),codes!B12,IF((C3="CCA13"),codes!B13,IF((C3="CCA14"),codes!B14,IF((C3="CCA15"),codes!B15,IF((C3="CCA16"),codes!B16,IF((C3="CCA17"),codes!B17,IF((C3="CCA18"),codes!B18,IF((C3="CCA19"),codes!B19,IF((C3="CCA20"),codes!B20,IF((C3="CCA21"),codes!B21,IF((C3="CCA22"),codes!B22,IF((C3="CCA23"),codes!B23,IF((C3="CCA24"),codes!B24,IF((C3="CCA25"),codes!B25,IF((C3="CCA26"),codes!B26,IF((C3="CCA27"),codes!B27,IF((C3="CCA28"),codes!B28,IF((C3="CCA29"),codes!B29,IF((C3="CCA30"),codes!B30,IF((C3="CCA31"),codes!B31,IF((C3="CCA32"),codes!B32,IF((C3="CCA33"),codes!B33,IF((C3="CCA34"),codes!B34,IF((C3="CCA35"),codes!B35,IF((C3="CCA36"),codes!B36,IF((C3="CCA37"),codes!B37,IF((C3="CCA38"),codes!B38,IF((C3="CCA39"),codes!B39,IF((C3="CCA40"),codes!B40,IF((C3="CCA41"),codes!B41,IF((C3="CCA42"),codes!B42,IF((C3="CCA43"),codes!B43,IF((C3="CCA44"),codes!B44,IF((C3="CCA45"),codes!B45,IF((C3="CCA46"),codes!B46,IF((C3="CCA47"),codes!B46,IF((C3="CCA48"),codes!B48,IF((C3="CCA49"),codes!B49,IF((C3="CCA50"),codes!B50,""))))))))))))))))))))))))))))))))))))))))))))))))))
Any ideas would be appreciated!
Using Excel 2002, SP3 on a Windows XP Machine.
I am trying to do a fairly simple thing that required a huge formula.
I have "labels" in column C3, on another worksheet (called 'codes') I have a list of new 'codes' for the things in C3. Each value in C3 can be one of 50. All 50 are represented in the 'codes'. There are approximately 1000 cells in C3. I would rather not do this by hand since the codes are things like "000000000000000000000000000100000000000"
This is my current formula:
=IF((C3="CCA01"),codes!B1,IF((C3="CCA02"),codes!B2,IF((C3="CCA03"),codes!B3,IF((C3="CCA04"),codes!B4,IF((C3="CCA05"),codes!B5,IF((C3="CCA06"),codes!B6,IF((C3="CCA07"),codes!B7,IF((C3="CCA08"),codes!B8,IF((C3="CCA09"),codes!B9,IF((C3="CCA10"),codes!B10,IF((C3="CCA11"),codes!B11,IF((C3="CCA12"),codes!B12,IF((C3="CCA13"),codes!B13,IF((C3="CCA14"),codes!B14,IF((C3="CCA15"),codes!B15,IF((C3="CCA16"),codes!B16,IF((C3="CCA17"),codes!B17,IF((C3="CCA18"),codes!B18,IF((C3="CCA19"),codes!B19,IF((C3="CCA20"),codes!B20,IF((C3="CCA21"),codes!B21,IF((C3="CCA22"),codes!B22,IF((C3="CCA23"),codes!B23,IF((C3="CCA24"),codes!B24,IF((C3="CCA25"),codes!B25,IF((C3="CCA26"),codes!B26,IF((C3="CCA27"),codes!B27,IF((C3="CCA28"),codes!B28,IF((C3="CCA29"),codes!B29,IF((C3="CCA30"),codes!B30,IF((C3="CCA31"),codes!B31,IF((C3="CCA32"),codes!B32,IF((C3="CCA33"),codes!B33,IF((C3="CCA34"),codes!B34,IF((C3="CCA35"),codes!B35,IF((C3="CCA36"),codes!B36,IF((C3="CCA37"),codes!B37,IF((C3="CCA38"),codes!B38,IF((C3="CCA39"),codes!B39,IF((C3="CCA40"),codes!B40,IF((C3="CCA41"),codes!B41,IF((C3="CCA42"),codes!B42,IF((C3="CCA43"),codes!B43,IF((C3="CCA44"),codes!B44,IF((C3="CCA45"),codes!B45,IF((C3="CCA46"),codes!B46,IF((C3="CCA47"),codes!B46,IF((C3="CCA48"),codes!B48,IF((C3="CCA49"),codes!B49,IF((C3="CCA50"),codes!B50,""))))))))))))))))))))))))))))))))))))))))))))))))))
Any ideas would be appreciated!
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
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,