if function

[structuresc]

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

[andy62]

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" wrote:

>
> I have a spreadsheet schedule for different steel I-beams such that cell
> A2 = 1-W, A3 = 2-W, A4 = 11-C, A5 = 21-L, etc. How do I setup an
> if/then function that converts the cell value to a description. For
> example, cell B2 would reference A2 and with the result "I-BEAM" based
> on a function in cell B2 like: IF(A2=W,"I-BEAM",IF(A2=C,
> "CHANNEL",IF(A2=L,ANGLE,"N\A")))
>
>
> --
> structuresc
> ------------------------------------------------------------------------
> structuresc's Profile: http://www.excelforum.com/member.php...o&userid=36067
> View this thread: http://www.excelforum.com/showthread...hreadid=558528
>
>

[structuresc]

The contents of the in column A represent the call number (the name of 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 represents wide flange I-beam named 2; A4 = 11-C represents C-channel shape named 11; A5 = 21-L represents angle shape member named 21. The numbers (names) are relatively random and therefore would require a list over 5000 cells per shape type [W,C,L]. The only info needed for column B is the shape.

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

[andy62]

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" wrote:

>
> The contents of the in column A represent the call number (the name of
> 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 represents
> wide flange I-beam named 2; A4 = 11-C represents C-channel shape named
> 11; A5 = 21-L represents angle shape member named 21. The numbers
> (names) are relatively random and therefore would require a list over
> 5000 cells per shape type [W,C,L]. The only info needed for column B
> is the shape.
>
> The backgournd info: each member is shown on a drawing and called out
> by its name; attached to the drawing is the excel spreadsheet
> [schedule] that itemizes and details each member. The analysis program
> output data produces an extensive list of members that does not
> duplicate the member name (number). The excel macro extracts and
> formats the necessary data accordingly. This formatted data is the
> schedule attached to the drawing.
>
>
> --
> structuresc
> ------------------------------------------------------------------------
> structuresc's Profile: http://www.excelforum.com/member.php...o&userid=36067
> View this thread: http://www.excelforum.com/showthread...hreadid=558528
>
>

[structuresc]

Thanks, that worked.