Hi,

I posted this but I haven't had any answer.

Does this mean that no-one understands or its impossible?

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;C OUNTIF($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)=Analys e!$B4;INDIRECT($S$6))))
3 - find max/av/min of col G per item using formula -
=IF(Analyse!$B4="";"";AVERAGE(IF(INDIRECT($S$5)=An alyse!$B4;INDIRECT($S$7))))
4 - count how many results per item there are using -
=IF(Analyse!$B4="";"";SUM(IF((INDIRECT($S$5)=Analy se!$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

See this...

http://www.decisionmodels.com/

--
Biff
Microsoft Excel MVP


"LiAD" wrote in message
...
Hi,

I posted this but I haven't had any answer.

Does this mean that no-one understands or its impossible?

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;COUNTI F($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)=Analys e!$B4;INDIRECT($S$6))))
3 - find max/av/min of col G per item using formula -
=IF(Analyse!$B4="";"";AVERAGE(IF(INDIRECT($S$5)=An alyse!$B4;INDIRECT($S$7))))
4 - count how many results per item there are using -
=IF(Analyse!$B4="";"";SUM(IF((INDIRECT($S$5)=Analy se!$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