Lookup text in cells across multiple sheets to display on dashboard/overview sheet

I have a workbook containing multiple sheets, each for a different project type.
Within these sheets are different test cases where multiple bugs can be logged. (all have the same template)
What I want to do is combine the list of bugs (and their descriptions) onto one sheet as an overview / dashboard

I've attached a spreadsheet, with the desired results in the JIRAs logged sheet.

What I need to do is say: If there is a value under the JIRA Logged column in a test case, then show it on the JIRAs logged spreadsheet.
If possible, the description in the column to the right of it should also show.

I have tried an INDEX with COUNTIF formula but could not get it working. It looked like this but with my data in it.
=INDEX(Item, SMALL(IF(($C$5=Category)*(COUNTIF(Sheet1!$E$9:E14,Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

Many members will not access file-sharing sites, so attach your sample workbook here. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

This modification to Glenn's formula will do it:

Please Login or Register  to view this content.

You'll need to change the E references to F for the second column.

1. Does Ali's response address your Q.

2. If not, let me check one thing. Are you trying to return all of the OPEN Jiras from each of the (currently) 3 Tabs, or something else??

The reason I am asking is that you seem to be just copying EVERYTHING from each sheet. If that is what you want, it can be done MUCH more simply!!

You've got the subtractions incorrectly ordered. Try this in column D (and adapt for the other columns):

Please Login or Register  to view this content.

You were 99.9% there. What was throwing you was the COUNTIFS bit at the end. There's a row counter in each of the INDEX-SMALL formulae (ROWS$1:1), which enables each result to be returned from that sheet in the order in which they appear. That needs to be re-set to 1 for each additional sheet. The COUNTIFS does that. It needs to look like this:
COUNTER-(COUNT of all copied values from sheet1) in the formula for sheet 2;
COUNTER-(COUNT of all copied values from sheet1 PLUS those in sheet2) in the formula for sheet 3;
COUNTER-(COUNT of all copied values from sheet1 PLUS those in sheet2 PLUS those in sheet3) in the formula for sheet 4, ad nauseam, or at least until EXCEL falls over.

Formula:

Please Login or Register  to view this content.

Not to worry. I just moved the second subtraction (Tab 2) and nested it with the first (Tab 1).

Incidentally, are there just 3 possible entries in your source data: open, closed and blank?