Excel Help with Formula

  • Thread starter Thread starter Ryan
  • Start date Start date
R

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)
 
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"))
 
Worked like a charm. Thanks.


Dave Peterson said:
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"))
 
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)
 
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
 
Back
Top