I am trying to do an SPC chart and within my criteria I need to look up at
sheet for two patient types 1 & 2. I can do this however I also want it to
bring in any patient type 1's first and then patient type 2 underneath with
it being an automatic sort ?????

this is what i have so far
=(IF(ROWS('Data extract table'!BM3:BM3)<=COUNTIF('Data extract
table'!BM$3:BM$56000,1),INDEX('Data extract
table'!BM$3:BM$56000,SMALL(IF('Data extract table'!BM$3:BM$56000=1,(ROW('Data
extract table'!BM$3:BM$56000)-ROW('Data extract table'!BM$3)+1)),ROW('Data
extract table'!BM3))),IF(ROWS('Data extract table'!BM3:BM3)<=COUNTIF('Data
extract table'!BM$3:BM$56000,2),INDEX('Data extract
table'!BM$3:BM$56000,SMALL(IF('Data extract table'!BM$3:BM$56000=2,(ROW('Data
extract table'!BM$3:BM$56000)-ROW('Data extract table'!BM$3)+1)),ROW('Data
extract table'!BM3))))))


It will give me patient type 1 but then shows num# for the patient type 2
underneath

It looks like you may be missing dollar signs infront of some of your $3
rows. with something this complicated I would use Tool Menu - formula
auditing - Evaluate formula and find which term is creating the #num error.

"kate" wrote:

I am trying to do an SPC chart and within my criteria I need to look up at
sheet for two patient types 1 & 2. I can do this however I also want it to
bring in any patient type 1's first and then patient type 2 underneath with
it being an automatic sort ?????

this is what i have so far
=(IF(ROWS('Data extract table'!BM3:BM3)<=COUNTIF('Data extract
table'!BM$3:BM$56000,1),INDEX('Data extract
table'!BM$3:BM$56000,SMALL(IF('Data extract table'!BM$3:BM$56000=1,(ROW('Data
extract table'!BM$3:BM$56000)-ROW('Data extract table'!BM$3)+1)),ROW('Data
extract table'!BM3))),IF(ROWS('Data extract table'!BM3:BM3)<=COUNTIF('Data
extract table'!BM$3:BM$56000,2),INDEX('Data extract
table'!BM$3:BM$56000,SMALL(IF('Data extract table'!BM$3:BM$56000=2,(ROW('Data
extract table'!BM$3:BM$56000)-ROW('Data extract table'!BM$3)+1)),ROW('Data
extract table'!BM3))))))


It will give me patient type 1 but then shows num# for the patient type 2
underneath