# DEFFINE NAME LIST IN FORMULA

I have a defined list titled sawlist and in that list are the letters i.e. W and in the next cell down L and so on. I wrote a formula that is     su=sum(if(sawlist=B9,B10))
I get a #value answer. My list is not numbers but letters and I am trying to write a letter in one cell and have the formula identify it as true if it matches a letter in another cell. How can I write this formula?

Thnaks Bill
Michael Fowler

Bill

Not entirely sure what you are after could you provide an file.

Note to compare two cells regardless if they are letters you can use something like

=IF(A1=B1, TRUE, FALSE)

Michael
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Billkronmiller

The formula is a rather long and exhaustive formula and I am trying to make a defined list to replace all the "or" in this long boat. :) Here is the whole formula I am using and I have many others in this spread sheet. The entire workbook is about 15 megs and all formulas.

=SUM(IF(AND(\$EU20="W",\$CG20<>0,OR(\$O20="COL",\$O20="BM",\$O20="GDR",\$O20="PC")),(((\$HO20)*2)+\$HP20)*2*1/\$AU\$3*\$CG20,IF(AND(\$EU20="W",\$CG20<>0,OR(\$O20="MBM",\$O20="MCOL")),(((\$HO20)*\$AT\$4*2)+\$AT\$6+\$HP20)*2*1/\$AU\$3*\$CG20,IF(AND(\$EU20="W",\$CG20<>0,OR(\$O20="RBM",\$O20="RGDR",\$O20="BPC")),(((\$HO20+\$AT\$6)*2)+\$HP20)*2*1/\$AU\$3*\$CG20))))-IF(AND(\$EU20="W",\$CG20<>0,\$O20<>0),(\$HR20+\$HS20))+(IF(\$AW20=0,(\$W20*\$CG20/60)))

My list contaings all the letters i.e. "W" & "col" I use on "" marks just the letters and I would like to take the short cut that would allow me to to have say \$EU20 = any of the letters in the saw list and without creating a much more difficult formula. In this formula I would have a number of different defined lists. Is this polssible?

Thanks Bill
Hmm,

I'm trying to make sense of your formula, but I'm not getting far. Can you post a spreadsheet with a simplified example that shows where your "sawlist" is and how you would want to involve that in a formula?

Once we get the general principles nailed, it may be easier to tackle the beast.

cheers, teylyn
It would definitely be easier with a workbook, though I think this is equivalent to what you posted but a bit shorter:

=IF(AND(\$EU20="W",\$CG20<>0),IF(OR(\$O20={"COL","BM","GDR"}),((\$HO20*2)+\$HP20)*2*1/\$AU\$3*\$CG20,IF(OR(\$O20={"MBM","MCOL"}),((\$HO20*\$AT\$4*2)+\$AT\$6+\$HP20)*2*1/\$AU\$3*\$CG20,IF(OR(\$O20={"RBM","RGDR","BPC"}),(((\$HO20+\$AT\$6)*2)+\$HP20)*2*1/\$AU\$3*\$CG20)))-IF(\$O20<>0,\$HR20+\$HS20))+(IF(\$AW20=0,(\$W20*\$CG20/60)))