Excel Help with Formula

[Ryan]

As you may have guess I am having some difficulties. If you take a
look at the formula below, I'm trying to get a certain value based on
if a cell contains a value or not. In the formula, it is suppose to
get the value based on what is entered in a different cell (column B)
and return the value by cell name. The problem is that it is not
returning the correct value, it is not even refering to the cell name,
but just printing it out as text.

What I would like to know is, is there a way to do what I'm trying to
do { =IF(A1<>"",B1&"_MOD",B1&"_STA") } or how I can go about doing it
a different way.

Thanks a bunch,
Ryan


Table is not exactly as show.
Table
A B C
1 * IN
2 * ST
3 IN
4
5 98 87
6 56 83


A5 cell name - IN_STA
B5 cell name - IN_MOD
A6 cell name - ST_STA
B6 cell name - ST_MOD

C1 formula - =IF(A1<>"",B1&"_MOD",B1&"_STA")
C2 formula - =IF(A2<>"",B2&"_MOD",B2&"_STA")
C2 formula - =IF(A3<>"",B3&"_MOD",B3&"_STA")

C1 should equal - 87 (get IN_MOD)
C2 should equal - 83 (get ST_MOD)
c3 should equal - 98 (get IN_STA)

 

[Dave Peterson]

One way:
=INDIRECT(IF(A1<>"",B1&"_MOD",B1&"_STA"))

But you could actually apply the =indirect() to any portion if you needed to
return something else:

=IF(A1<>"",INDIRECT(B1&"_MOD"),"whatever you want here")

This would work, too:

=IF(A1<>"",INDIRECT(B1&"_MOD"),INDIRECT(B1&"_STA"))

 

[Ryan]

Worked like a charm. Thanks.

One way:
=INDIRECT(IF(A1<>"",B1&"_MOD",B1&"_STA"))

But you could actually apply the =indirect() to any portion if you needed to
return something else:

=IF(A1<>"",INDIRECT(B1&"_MOD"),"whatever you want here")

This would work, too:

=IF(A1<>"",INDIRECT(B1&"_MOD"),INDIRECT(B1&"_STA"))

 

[Kyle]

This may just be a simplification of a previous question but I am still having problems. I am using the following IF statement atempting, if the statement is true, to get a calculation out, however I am getting only text

IF(A1-30>0,"A1-30","0"

Assuming A1 is 32 than 30 I get (A1-30) instead of the desired value (2)

 

[Gord Dibben]

Kyle

Remove the quotes from around the second A1-30

Also not needed around the 0

=IF(A1-30>0,A1-30,0)

Gord Dibben Excel MVP