IF formula - Skip rows that dont meet criteria

[AusBec]

I have a spreadsheet which contains data for crime incidents occurring in a month. I have created a worksheet which uses "IF" statements to pull in data that occurred within the last 24 hours only. I would like to create a report that can be printed which has a crime category and lists all the incidents for that category below that occurred during the day. The issue I am having is that all "IF" and "LOOKUP" statements are line for line and I want to be able to skip blanks or where it is not the category in the title.

Sorry I tried to attach the spreadsheet to show what I was talking about but it didnt work.

Thanks in advance for any help.

[Kieran]

AusBec,

Form my understanding of your problem, it appears that the monthly and daily reports can be created with Pivot Tables.

Failing that (can be daunting teh first few times, but is really good tool) try using the autofilter or advanced filter tools. They should remove the blank cells quickly.

Look up the standard Excel function in online help, it may be what you need. I cannot add much more deatil, as the worksheet could not be atttached.

[sglife]

I think this is what you need

http://www.excelforum.com/excel-work...-function.html

[AusBec]

Thanks guys.

Yes, sglife, this is exactly what I am trying to achieve, however, I am pulling in data from different spreasheets etc so trying to figure out how to convert this formula to fit my sheets. I have figured most of it out, however, what I am having the most trouble with is the row formula's. Is it possible at all to see the formula's in the excel sheet provided for the other variables as well such as S2 and S3, this might give me a better understanding of what variables I need to put in the row formula's for my own document. So far they are coming up blank

Thanks

[AusBec]

Sorry, I have been playing with the formula all day and have noticed two problems for getting it working:

{=IF(COUNTIF($A$1:$A$200,$E$2)>=ROW(A1),INDEX($B$1:$B$200,SMALL(IF($A$1:$A$200=$E$2,ROW($A$1:$A$200)),ROW(A1))),"")}

Firstly, as above there are the {} brackets around the formula which I dont have in mine, dont know whether this is related to it not working.

Secondly when validating my formula I have found it is the section highlighted which I cant get to work. In the example given, it produces an array (e.g. false, false, false, 2, false, 3,false); however, with my code it is just producing a one off "Fasle" as the entire answer which is why I am getting nothing in the cell.

Any help would be greatly appreciated.

Cheers

[danimoor82]

Click inside of the formula and simultaneously select: CTRL + SHIFT + ENTER. It worked for me.

[Pete_UK]

Good to know that, danimoor, but you are replying to a thread which is almost 6 years old, so I doubt if the original contributors are still listening.

Pete

[danimoor82]

This is true, Pete. But when I was searching for a solution to my inquiry regarding the post, it felt like I only received a partial answer. I had to research the final part of the answer. I just figured I would save the next analyst some time by replying to the last post. I was thinking about the person that may stumble across this post after me.