+ Reply to Thread
Results 1 to 21 of 21

Best way to Index/Match across multiple worksheets?

  1. #1
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    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!!!
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Best way to Index/Match across multiple worksheets?

    Your file seems corrupted. Can you fix and upload?

    At any rate, I'd recommend using PowerQuery to do the job (free add-in from Microsoft for Excel 2010).

    Other methods that comes to mind are...
    1. Use INDIRECT to dynamically reference sheets

    2. Use Pivot Table wizard to consolidate multiple ranges into single pivot table report.
    https://support.office.com/en-us/art...1-e9fc8adeeeb5

    3. Nested IFERROR(Index,Match) construct
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    Re: Best way to Index/Match across multiple worksheets?

    Sorry about that, is this better?
    Attached Files Attached Files

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Best way to Index/Match across multiple worksheets?

    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.

  5. #5
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    Re: Best way to Index/Match across multiple worksheets?

    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

  6. #6
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    Re: Best way to Index/Match across multiple worksheets?

    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...

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Best way to Index/Match across multiple worksheets?

    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?

  8. #8
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    Re: Best way to Index/Match across multiple worksheets?

    @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...

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Best way to Index/Match across multiple worksheets?

    Ok, let me think on best approach.

    Are you opposed to using VBA?

  10. #10
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    Re: Best way to Index/Match across multiple worksheets?

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

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Best way to Index/Match across multiple worksheets?

    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.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    Re: Best way to Index/Match across multiple worksheets?

    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....

  13. #13
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Best way to Index/Match across multiple worksheets?

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

  14. #14
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    Re: Best way to Index/Match across multiple worksheets?

    @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!!
    Attached Files Attached Files

  15. #15
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Best way to Index/Match across multiple worksheets?

    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?

  16. #16
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    Re: Best way to Index/Match across multiple worksheets?

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

  17. #17
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    Re: Best way to Index/Match across multiple worksheets?

    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.

  18. #18
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Best way to Index/Match across multiple worksheets?

    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.

  19. #19
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    Re: Best way to Index/Match across multiple worksheets?

    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!~~~

  20. #20
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Best way to Index/Match across multiple worksheets?

    Here you go.
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    60

    Re: Best way to Index/Match across multiple worksheets?

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Index Match with multiple worksheets
    By Angry Alex in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-12-2015, 05:34 AM
  2. INDEX and MATCH multiple worksheets
    By bluefiesta in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-30-2014, 03:17 PM
  3. Index & match SUM multiple worksheets
    By Thito in forum Excel General
    Replies: 12
    Last Post: 04-02-2014, 05:15 PM
  4. Index and Match across multiple worksheets
    By GTHORE in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-09-2013, 01:05 PM
  5. Index match multiple worksheets
    By rrivera616 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-28-2013, 07:03 PM
  6. index and match across multiple worksheets
    By pertenax in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-21-2012, 03:15 AM
  7. Excel 2007 : Index and Match using multiple worksheets
    By lola12345 in forum Excel General
    Replies: 1
    Last Post: 10-27-2011, 03:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1