Complex if function help

**Adamlearnexcel** · 12-17-2012, 03:19 AM

Dear, everyone
please refer the attached document.
there is a complex if function in "status code" column
I want to adjust this formula so that, when column J is "SGEB", "6" will be dispalyed in status column.
and other information remain the same.
only first two row of status code column wil change to 6. the rest remain the same

**imran91** · 12-17-2012, 03:48 AM

instead of using long formula, my idea is you can just use a reference cell and then use vlookup.

**dredwolf** · 12-17-2012, 03:52 AM

in AH2:
=IF(J2="SGEB", "6","")
copy down
Hope this helps

**djapigo** · 12-17-2012, 03:58 AM

Hi Adam...

Sorry, but I couldn't really follow the formula to make sense of it... however, I found a couple of items wrong...
In the 2nd formula, you referenced T2424?!?
Also, you were comparing a column to today's date, but the column has a text date... make sure you are comparing apples to apples...

Sorry I can't help more...

Dennis

**dredwolf** · 12-17-2012, 04:10 AM

aah...darnit...knew it could not be that easy...whats the info that separates the status column?

**dredwolf** · 12-17-2012, 04:14 AM

"SGEB" is the comparison, but what makes us able to discern what two rows should have the "6" ?

**dredwolf** · 12-17-2012, 04:28 AM

well you can use this in AH2, copied down :
=IF(AND(J3="SGEB",NOT(AND(AH2="6",AH1="6"))), "6","")

but I THINK you want something different, just not sure where you want the separation to occur,,,

**dredwolf** · 12-17-2012, 04:46 AM

Edit -
Disregard...status code is already comparing to "SGEB", but I still need the info on WHAT creates the separation in the status cell?

**Adamlearnexcel** · 12-17-2012, 12:05 PM

Everyone, thanks a lot. actually, I even dont know how to express this problem, because I cannot understand this IF statement. So, I try to figure it out first