I am trying to get the following to work, but it's not.
=IF($C$9="Block","N/A",VLOOKUP($D$8,Dims,IF(and($E$9="Short",$F$9="45°",5,if(and($E$9="Long",$F$9="45°",6,IF(and($E$9="Short",$F$9="90°",7,8))))))))
it will not accept the degree symbol "°" (alt-0176). Cells E9 & F9 are drop down lists.
Try to use it like this
LEFT($F$9,LEN($F$9)-1)="45"
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I have never seen or used "LEFT" before. If you don't mind, would you show me how to use it in that string?
AlKey looks to be offline right now, so I will fill in.
LEFT() is 1 of 3 tools used to extract a string from text or number (=LEFT() =MID(), =RIGHT() ). They all work by looking in a cell, and extracting a defined number of characters for a defined length. Their syntax is...
=LEFT(cell-ref,number of chatcters)
=LEFT() always starts at the very begining of a cell contents, and returns however many characters you tell it to
A1 = abcdef =LEFT(A1,3) will give you abc
=MID(cell-ref, starting number of characters, number of characters)
=MID() starts at the location you very specify with "starting number of characters", and returns however many characters you tell it to.
A1 = abcdef =MID(A1,3,3) will give you cde
=RIGHT() always start at the very end of a cell contents, and returns however many characters you tell it to
A1 = abcdef =RIGHT(A1,3) will give you def
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I just tried it again (below), I am still getting the error msg 'there's a error in your formula'.
=IF($C$9="Block","N/A",VLOOKUP($D$8,Dims,IF(and($E$9="Short",LEFT($F$9,LEN($F$9)-1)="45",5,if(and($E$9="Long",LEFT($F$9,LEN($F$9)-1)="45",6,IF(and($E$9="Short",LEFT($F$9,LEN($F$9)-1)="90",7,8))))))))
This is in cell D15, maybe there is something else that's causing the error that I haven't learned yet.
see attached does this return the correct result?
I have added another lookup table to return the column number, have also joined the two cells to give the Long 90°.
Windy
It looks like the problem was with the formula's structure.
Please see attached file with corrected formula in D15
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks