If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|