Prev: First Day of the month
Next: Trying to match 1 value to multiple columns
From: LiAD on 25 Mar 2010 12:34
Afternoon,

Sorry i forgot to put in the formulas concerned. Second time lucky!

I have an excel sheet of 10 cols and 200 rows on sheet 1.
Sheet 2 has a table which searches through the list of data in sheet 1 to

1 - produce a list of unique items using the formula -

=IF(IF(ISNA(MATCH(0;COUNTIF($B$3:B3;Réleves!$D$3:$D$5001);0));"";INDEX(Réleves!$D$3:$D$5001;MATCH(0;COUNTIF($B$3:B3;Réleves!$D$3:$D$5001);0)))=0;"";IF(ISNA(MATCH(0;COUNTIF($B$3:B3;Réleves!$D$3:$D$5001);0));"";INDEX(Réleves!$D$3:$D$5001;MATCH(0;COUNTIF($B$3:B3;Réleves!$D$3:$D$50001);0))))
2 - find max/av/min of col F per item using formula -
=IF(Analyse!$B4="";"";MAX(IF(INDIRECT($S$5)=Analyse!$B4;INDIRECT($S$6))))
3 - find max/av/min of col G per item using formula -
=IF(Analyse!$B4="";"";AVERAGE(IF(INDIRECT($S$5)=Analyse!$B4;INDIRECT($S$7))))
4 - count how many results per item there are using -
=IF(Analyse!$B4="";"";SUM(IF((INDIRECT($S$5)=Analyse!$B4)*(INDIRECT($S$6)>0);1;0)))

Indirect is used to drive the address so as the file only searches through
rows which contain data.

When i try and use the filters on sheet 1 to search specific items etc the
file is very long to update.

Is there a reason why it is so long to activate when i try to filter?
How can i speed this up?

Thanks
Lindsey
 | 
Pages: 1
Prev: First Day of the month
Next: Trying to match 1 value to multiple columns