Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost - Functions on filtered data
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 Lindsey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions on filtered data | Excel Worksheet Functions | |||
Return filtered values into report worksheet based on filtered valueon the data worksheet | Excel Worksheet Functions | |||
Data Validation - repost | Excel Discussion (Misc queries) | |||
REPOST: Count certain records in filtered data | Excel Discussion (Misc queries) | |||
Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) |