Pulling information from several sheets depending on criteria

Hello

I'm trying to pull together an overview sheet for a spreadsheet that has 10+ sheets, each of which have the same information however for different geographic locations.

These sheets document IT and server outages in each region together with lots of information about each issue. A single outage has a single row with multiple columns.

What I would like to do is look at each column (outage) and check if the outage is "under investigation" or "closed".

If the status cell contains "under investigation", I would like selected cells to appear on the overview sheet. Once a outage is closed, it would not be shown on the overview sheet.

I can count how many outages are currently open : =COUNTIF(INDIRECT("'" & "USA" & "'!$D$1:$D$500"),"Open")

However how do I then display the selected cells on the overview sheet....

Something along the lines of :

If A1 on sheet "USA" = "open" then sheet "OverView".A2 = "USA".A2

However how would I then make it more dynamic on the overview sheet. As outages are closed and the number of rows in "USA" increases, the "USA".A2 needs to be more dynamic, so in the overview rather than reference a specific row, in this case A, it looks for the first open case and gets the row information by itself.

Is such a thing possible, doing it with a database would be easier, however what i have is excel ?

Thank you
A

Hello Qt11,

Welcome to forum!!!

Please attach the sample workbook without sensitive information. It would be easy to provide solution by experts.

Regards,
Humac

Thanks Humac,

I'm looking through the forum and see something similar to what I am trying to achieve : http://www.excelforum.com/excel-gene...-criteria.html

Please see the attachment in the above post.

I2 contains the keyword which then creates a dynamic list in columns M onwards, change the I2 value and the list on the right changes.

This is almost exactly what I'm looking for.

I've attached a example of what I'd like to create. the overview pulls information from the other tabs where the status (D) = open

However I'd like it to be dynamic in the same way that the attachment in the link above works. So only the open cases are displayed in the overview tab and the number of rows in overview tab increases and decreases depending on the content of the other tabs...

Hope this makes sense

I've tried to adapt the logic of the example found : http://www.excelforum.com/excel-gene...-criteria.html

however just can't seem to get it to work, does any one have any guidance which may steer me towards a solution?

Thank you
A

anyone ?