if function

[structuresc]

I have a spreadsheet schedule for different steel I-beams such that cel
A2 = 1-W, A3 = 2-W, A4 = 11-C, A5 = 21-L, etc. How do I setup a
if/then function that converts the cell value to a description. Fo
example, cell B2 would reference A2 and with the result "I-BEAM" base
on a function in cell B2 like: IF(A2=W,"I-BEAM",IF(A2=C
"CHANNEL",IF(A2=L,ANGLE,"N\A"))

 

[Guest]

I'm not sure I understand the contents of your cells in column A for example,
is "1-W" a formula or a text label?, but I think I can still help. Rather
than all those nested IF statements in column B, I think you'd be better off
with a VLOOKUP. Set up another worksheet (let's call it "RefLabels") with
all the possible column A results down the first column and, in a second
column, the translation text for each. Then in cell B2 in your original
worksheet use the formula:

=VLOOKUP(RefLabels!$A$1:$B$1000,A2)

You can copy that formula to all the other cells in column B and it should
work.

HTH

 

[structuresc]

The contents of the in column A represent the call number (the name o
the member in the system) and member shape (W,C.L). In the example,
A2 = 1-W represents wide flange I-beam named 1; A3 = 2-W represent
wide flange I-beam named 2; A4 = 11-C represents C-channel shape name
11; A5 = 21-L represents angle shape member named 21. The number
(names) are relatively random and therefore would require a list ove
5000 cells per shape type [W,C,L]. The only info needed for column
is the shape.

The backgournd info: each member is shown on a drawing and called ou
by its name; attached to the drawing is the excel spreadshee
[schedule] that itemizes and details each member. The analysis progra
output data produces an extensive list of members that does no
duplicate the member name (number). The excel macro extracts an
formats the necessary data accordingly. This formatted data is th
schedule attached to the drawing

 

[Guest]

Although there are many pros on this board, I am not one of them, so if this
doesn't help I will step aside and let one of them take over. It seems from
your response that the challenge is just to ignore the "1-" in "1-W" and pick
out the W (or C, or L). If that is correct, and the data always ends with
the one desired letter at the end, the RIGHT function should help:

=RIGHT(A2,1) will return "W" (no quotes) when the value of A2 is "1-W" (no
quotes).

So you could embed that function into your IF statements:

IF(RIGHT(A2,1)="W","I-BEAM",IF(RIGHT(A2,1)="C","CHANNEL",IF(RIGHT(A2,1)="L",ANGLE,"N\A")))

 

[structuresc]

Thanks, that worked.