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

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

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

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

Thanks, that worked.


--
structuresc
------------------------------------------------------------------------
structuresc's Profile: http://www.excelforum.com/member.php...o&userid=36067
View this thread: http://www.excelforum.com/showthread...hreadid=558528