Auto Population of Summary Sheet

**Ranbir** · 05-11-2010, 10:17 AM

I am attached herewith a sample data, where i want to auto populate my summary sheet in totallity.

Sheet LR_SUMMARY

the summary sheet has 3 section

Day wise and Quality wise i have Auto Populated. but in supplier wise I am facing some problem.

I am able to populate the firs supplier name in the summary sheet by using the formula

=INDEX(LR_CNF!$P$5:$P$26,MATCH(0,COUNTIF(LR_CNF!$P$5:$P$26,"<"&LR_CNF!$P$5:$P$26),0))

However, the susequent supplier are not being populated, I am using the following formula

=IF(COUNTIF(LR_CNF!$P$5:$P$26,">"&I7), INDEX(LR_CNF!$P$5:$P$26, MATCH(COUNTIF(LR_CNF!$P$5:$P$26,"<="&I7), COUNTIF(LR_CNF!$P$5:$P$26,"<"&LR_CNF!$P$5:$P$26),0)),"")

SHEET LR CNF

we are Generating SL Code and LCR Code to update sheet SL-1, SL-2 etc...

Is there any more simple formula to calculated LCR Code other than used by me.

Thanks in anticipation.

**JBeaucaire** · 05-11-2010, 10:39 AM

Your G6 formula is looking in the wrong place, it needs to look at G5 to continue the list:

=IF(COUNTIF(LR_CNF!$P$5:$P$26,">"&G5), INDEX(LR_CNF!$P$5:$P$26, MATCH(COUNTIF(LR_CNF!$P$5:$P$26,"<="&G5), COUNTIF(LR_CNF!$P$5:$P$26,"<"&LR_CNF!$P$5:$P$26),0)),"")

**Ranbir** · 05-12-2010, 01:29 AM

Thank's a lot Beaucaire.

Is there any other way to generate LCR Code other than the way used in the attached sheet.
B'coz in my ways its too long.

Ranbir