EXCEL: If-then statements

pologoalie8908

Honorable
Mar 3, 2013
3
0
10,510
0
I have a row of data: A1=ticketnumber B1=priority(low,med,high) C1=description D1=persons name E1=orginizations F1=last modified G1= date reported H1=Todays date J1=days open K1= days since last update

i basilcy want B1 to change names from low,med,high based on the values in J1. So if J1 is higher than 10, i want B1 to change automaticly to the word medium, and if j1 is greater than 30 to automaticaly say high

please help
 

pologoalie8908

Honorable
Mar 3, 2013
3
0
10,510
0
OK well the words in B are reported from a web serivce i use and i want them to stay like that UNLESS "J" gets to 10 and 30. So B might have the word medium already, but i dont want to apply that formula becqause it will change it to low
 

getochkn

Splendid
Moderator


Can't really be done that way, because column B has to have the above formula in it (mine or USA's, both work the same, different approach) in the first place to calculate the answer to show in B. Where would you put this formula then if it's not in B?
 

pologoalie8908

Honorable
Mar 3, 2013
3
0
10,510
0


in J?

 

getochkn

Splendid
Moderator


J has the value, 10,20,30, whatever. B has a word, low, med, high. You need the formula "=IF(AND(B1>10,B1<30),"med",IF(B1>30,"high","low"))" in column B in order to change the words in B. you can't put this formula anywhere and have it change B. The worksheet functions can only return a value, not set it.

What you want is programming done in VBA programming. This would let you write a function that would change the values of B based on any cell, etc.

I could do it, but it seems like this is for your work, and no offence, but I'm not getting paid to do this, so I don't have the time to write, debug, etc an entire VBA function to do this. I think you need some Excel training if they are expecting you to do this.

Alternative would be to not change the words in the cell, but you conditional formatting to change the color of the cells in column B based on a value in column J. It wouldn't change the words but you could do, change background to green if J< 10, yellow if it's between 10 and 30 and Red if it's over 30, or whatever, so you can visually look and see, these ones are RED, they are over 30, etc.
 

USAFRet

Splendid
Moderator


Then the problem needs to be defined more. B already having other data complicates things.
What you're looking for is the word "Sometimes". As in, "I want it to always show this, but sometimes I don't"

And as with getochkn, I ain't getting paid for this.
Put that formula in another column....X...and ignore the web service derived value in B.
 

getochkn

Splendid
Moderator
Gave yours the best answer, was a bit more slick than mine and avoided the AND. Both did the same thing but I like streamlined code when possible and yours was it.

Also, since the OP seemed to not like our answers about doing his work for him. lol. I've done custom Excel macro work for $100/hour for companies before. I'll point in the right direction to someone on here, but without that $100, I have better things to do than do their job and write the whole thing for them.
 
Thread starter Similar threads Forum Replies Date
B Apps General Discussion 2
N Apps General Discussion 1
S Apps General Discussion 5
N Apps General Discussion 2
L Apps General Discussion 5
B Apps General Discussion 4
K Apps General Discussion 1
R Apps General Discussion 1
7 Apps General Discussion 37
J Apps General Discussion 11
E Apps General Discussion 15
L Apps General Discussion 5
S Apps General Discussion 1
A Apps General Discussion 1
E Apps General Discussion 1
T Apps General Discussion 1
B Apps General Discussion 1
B Apps General Discussion 23
B Apps General Discussion 2
H Apps General Discussion 13

ASK THE COMMUNITY