Excel IF Formula Question 1

[rjm65]

I have a drop down list with 22 or so choices, they are for various kinds of woods, ie. maple 3/4", mahogany 3/8" , etc. All the woods can be broken down into two categories, solid and plywood. I would like it set up so that when I select a wood that is solid, a cell will display the text "Board Feet" and if a wood that is plywood that is chosen for it to display in that same cell "Square Feet." I tried using IF argument, but it limits me to 7 choices and I have 11 types of wood that are ply and 11 that are solid.
Thanks,
Raymond

 

[ETID]

How do we tell from the drop down that it is ply or not?

 

[rjm65]

The items in the drop down list are labeled as L Pine 3/4", P Mahogany 3/8", etc. If an item is solid it has an L prefix for lumber, or P for Ply. Is there someway to use a wildcard in a IF formula?

 

[straybullet]

=IF(B1="*plywood","Sq Ft",IF(B1="*solid","Board Ft",""))

using the wildcard this way will allow you to get around the 7 limit if your choices are set up as:
mahogany 1/8" plywood
oak 1/2" solid
etc...

Let them hate - so long as they fear... Lucius Accius

 

[sfvb]

It sounds like you're trying to use a nested IF statement...

=IF(A1="Butternut","Board Feet", IF(A1="Hickory","Board Feet", IF(A1="Quarter Sawn White Oak", "Board Feet", IF A1="Baltic Birch","Square Feet",...

Since you only have 2 outcomes, Board Feet or Square Feet, (3 if you want to code for an error condition)...
You should use the "OR" function.

=IF(OR(A1="Butternut",A1="Hickory",A1="Quarter Sawn White Oak"),"Board Feet",IF(OR(A1="Baltic Birch",A1="Honduran Mahogany"),"Square Feet","Error"))

Steve

 

[sfvb]

Raymond,

Sorry, I was answering as you posted.

=IF(LEFT(A1)="L","Board Feet", "Square Feet")

will work for what you described.

Steve

 

[rjm65]

Thanks Steve, it's just what I needed. One other question, in the formula =IF(LEFT(A1)="L","Board Feet", "Square Feet") is going into a locked cell in a protected sheet, is there anyway that the second choice, can be eliminated and the user be allowed to insert the value? Like IF (LEFT(A1)="L","Board Feet", otherwise it would let the user type in a value.

If all possible, I'd like to leave that cell locked.

Thanks again,
Raymond