Best way to Index/Match across multiple worksheets?

Hi all! I have a file attached. It is a risk assessment with Level 1-5 risk levels. Level 1 and 2 are in A2 on both tabs.

A few risks have numbers under column E and column F, some do not.

What I want to do is open a third tab, and pop a formula in that will extract only the risks with a number under Column E and Column F.

For example, in tab 1..3 of the risks have a number under Column E (Impact) and Column F (Likelihood).

I would want a formula (in different columns of tab 3 which I would add), that will extract all the information and the associated risk level information.

So the first row of information would read

Donuts; Donuts Flavor; Eating; Utensil; Fork; Risk of eating donuts with a fork and making a mess; 3; 3

Does that make sense?? I really need your expertise on this and THANK YOU!!!

Sorry about that, is this better?

Yes it is... but ouch!!

Merged cells are evil. Avoid it at all cost. It's nothing more than visual fluff and it complicates data analysis down the line.

http://datascopic.net/xlcaliber-7deadlysins/
http://www.techrepublic.com/blog/10-...u-in-the-butt/
http://www.notjustnumbers.co.uk/2013...-to-merge.html

Are you opposed to restructuring your data table? Without doing that, you are going to have a very hard time accomplishing what you are trying to do.

Ughhh, that is what I thought. Unfortunately, I am not the owner of this report. I just need to be able to dynamically link to it and extract data from it for a dashboard.....any suggestions would help

Alternatively, if I was to unmerge the cells....would it be possible to pull this off? I could always create a copy of the report and unmerge the cells myself...it's only updated twice a year so there really is no need for a dynamic update...

Let me see if I can think of a way. Nothing comes immediately to mind.

I reserve a special place for those that use merged cells (except in final display of dashboard). :p

I wonder, is there source data for this?

@CK76, there is source data, but it pulls from 100 different sources and software spits this out. We nthen have to go through and rank Impact and Likeliness. My whole effort stems from extracting data that have those fields filled in. They are not filled in until the software spits out this risk assessment in its current form...

Ok, let me think on best approach.

Are you opposed to using VBA?

No i don't think so, I would need to apply it to like 35 tabs though....would that be a problem?

No, number of tabs/sheets is not an issue.

Try the attached sample by going to "Consolidated" sheet and clicking on the Generate Report button.

Please Login or Register  to view this content.

Holy Crap CK76!!! This looks amazing!! How can I ever thank you!! I'm gonna use this one the real risk assessment I working from....

You are welcome and thanks for the rep I enjoyed the challenge.

@CK76, I have one small thing to ask if you would be so kind. I have attached a shell of what the real document looks like. Columns and top right data are slightly different in location. Would you mind adjusting the VBA to reflect the real location? I am not sophisticated enough to do it ony my own, I'm sorry!!

Let me get back to you on that. I'm headed out and won't have access to PC until late tonight.

One question. Do you need Group1 & Group 2 columns as well?

No no, not at all. CK76, you're the man!!! Seriously...!! I wish I was this smart...

No Group 1 or Group 2. The only other thing is, Impact and Likelihood are 1-5 only. How can I thank you bro!! You are doing me a great service.

Sorry about late reply. Had a busy weekend.

With your updated sheet structure. Try this code. Just small adjustment made to delete row 1 and column 1 before putting range into array.

Please Login or Register  to view this content.

Thank you for your help CK! Would you mind embedding it in the file for me and attaching it back here? I really appreciate it, you saved my hyde brother!~~~

Here you go.

You are theman! Thanks again, I added to your reputation....you deserve every bit and more!