• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup function with IF

I

ichristan21

New Member
Hi Everyone,

Newbie here, please help with this formula. The below mentioned formula are in separate sheets however I want to integrate the two into one column range with lookup capabilities. See attach file for sample.



Master Database Sheet - Drop Notice Status:
'=IF([@Withdraw]<>"","Withdraw",(IF([@Transferred]<>"","Transferred",(IF([@[In-Active2]]<>"","In-Active",(IF([@[Drop Notice Date2]]<>"","Dropped",(IF([@[Reinstated Date4]]<>"","Reinstated",(IF([@[4th Drop Notice Date]]<>"","4th DN on Process",(IF([@Absence4]<>"","4th DN to be Issued",(IF([@[Dropped Date2]]<>"","Dropped",(IF([@[Reinstated Date3]]<>"","Reinstated",(IF([@[3rd Drop Notice Date]]<>"","3rd DN on Process",(IF([@Absence3]<>"","3rd DN to be Issued",(IF([@[Dropped Date]]<>"","Dropped",(IF([@[Reinstated Date2]]<>"","Reinstated",(IF([@[Final Drop Notice Date]]<>"","Final DN on Process",(IF([@Absence2]<>"","Final DN to be Issued",(IF([@[Reinstated Date]]<>"","Reinstated",(IF([@[Drop Notice Date]]<>"","1st DN on Process",(IF([@Absence]<>"","1st DN to be Issued",)))))))))))))))))))))))))))))))))))


Sem1 Sheet - Final Warning Status:
=IF('Sem1'!$O$4:$O$320="Admin","Final Warning Issued",(IF('Sem1'!$N$4:$N$320<>"","Final Warning On Process",(IF(M9>=30,"Final Warning to be issued",(IF('Sem1'!$K$4:$K$320="Admin","1st Warning Issued",(IF('Sem1'!$J$4:$J$320<>"","1st Warning On Process",(IF('Sem1'!$I$4:$I$320<>"","1st Warning to be issued",)))))))))))


Thanks in Advance!
 
Hi ,

I think it would be better if you could just explain in plain English :
  1. In which cell(s) you want a formula
  2. What the formula(s) should do
Narayan
 
Hi Narayan,

I want the formula to be placed in Sheet(Sem1) P4 up to P10, example: if student 400978 Evelyn reach 20-29 hours of absences. 29hours encoded in I10 will show "First Warning to be Issued" appear in P10, and once the date has been filled in J10, "First Warning on Process" will appear in P10, and fill K10 "First Warning Issued" appear on P10 and so on for the Final Warning which needs 30-40 hrs absences.

Sem1 Sheet - Final Warning Status: in P10
=IF('Sem1'!$O$4:$O$320="Admin","Final Warning Issued",(IF('Sem1'!$N$4:$N$320<>"","Final Warning On Process",(IF(M9>=30,"Final Warning to be issued",(IF('Sem1'!$K$4:$K$320="Admin","1st Warning Issued",(IF('Sem1'!$J$4:$J$320<>"","1st Warning On Process",(IF('Sem1'!$I$4:$I$320<>"","1st Warning to be issued",)))))))))))

However

I want to add the below formula to P10 but with look capabilities that match Student ID from two sheets because the below formula is from another sheet(Master Database) in which it do the same IF function.

Master Database Sheet - Drop Notice Status:
'=IF([@Withdraw]<>"","Withdraw",(IF([@Transferred]<>"","Transferred",(IF([@[In-Active2]]<>"","In-Active",(IF([@[Drop Notice Date2]]<>"","Dropped",(IF([@[Reinstated Date4]]<>"","Reinstated",(IF([@[4th Drop Notice Date]]<>"","4th DN on Process",(IF([@Absence4]<>"","4th DN to be Issued",(IF([@[Dropped Date2]]<>"","Dropped",(IF([@[Reinstated Date3]]<>"","Reinstated",(IF([@[3rd Drop Notice Date]]<>"","3rd DN on Process",(IF([@Absence3]<>"","3rd DN to be Issued",(IF([@[Dropped Date]]<>"","Dropped",(IF([@[Reinstated Date2]]<>"","Reinstated",(IF([@[Final Drop Notice Date]]<>"","Final DN on Process",(IF([@Absence2]<>"","Final DN to be Issued",(IF([@[Reinstated Date]]<>"","Reinstated",(IF([@[Drop Notice Date]]<>"","1st DN on Process",(IF([@Absence]<>"","1st DN to be Issued",)))))))))))))))))))))))))))))))))))

Thanks,
 

Attachments

You must log in or register to reply here.
Back
Top