+ Reply to Thread
Results 1 to 10 of 10

INDEX/MATCH across several worksheets

  1. #1
    Registered User
    Join Date
    04-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Post INDEX/MATCH across several worksheets

    Hi all,

    This is my first post in the forum. I have greatly benefitted from the expert advice given here and hope you guys can help me with the following problem:

    Status quo:
    I have 12 workbooks, each of them structured in the same way in order to retrieve the data from an OLAP cube. Colum B:B contains financial accounts (Net Interest Income, Net Dealing Income, Liquidity Costs, etc.). In row C11:Z11 I have my 'managed regions' with the revenues in the individual accounts. Each workbook contains different company businesses.

    Problem:
    I am trying to rearrage the revenue data from the workbooks in a separate workbook that is structured as follows:

    Column B: Accountlines
    Column C: Accountline Shortcut
    Column E: Region
    Column F: Region Unique Identifier
    Column G: Values

    So far I have come up with a solution to extract the revenue data via an INDEX/MATCH function for one workbook only.

    =INDEX(AIS!$C$12:$Z$186,MATCH('Rearranged'!$B5,AIS!$B$12:$B$186,FALSE),MATCH('EMC Rearranged'!$E5,AIS!$C$11:$Z$11,FALSE),1)

    However I cannot INDEX/MATCH across more than one workbook. I played around with a LOOKUP/CHOOSE combination that I found in a thread which I believe has some similiarity to my problem but was not successful.

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

    I would appreciate if someone can help me finding a solution.

    Many thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: INDEX/MATCH across several worksheets

    Do all the business tabs have the same regions and in the same order?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: INDEX/MATCH across several worksheets

    Quote Originally Posted by NBVC View Post
    Do all the business tabs have the same regions and in the same order?
    Hi, yes all the business tabs have the same regions and in the same order.

    Thanks for looking into this.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: INDEX/MATCH across several worksheets

    Does something like this work then?

    Create a list of the business names as per the tab names in a column somewhere, and give that list a name (like Businesses) (just select the range and type Businesses in the Name Box to the left of the formula bar).

    Then use formula in F5:

    Please Login or Register  to view this content.
    copied down

  5. #5
    Registered User
    Join Date
    04-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: INDEX/MATCH across several worksheets

    Works like a charm. Great stuff!

    One thing that I forgot to mention, I would like to pivot the rearranged data by business. Your solution is great, but it gives me the the sum of the accountline across the individual regions for the businesses.

    How can I make this work if I add the 'business' (Business 1, Business 2, Business 3, etc.) as a further variable to my 'Rearranged Data' worksheet? (Colum F, and Values in Colum G)

    I don't mind creating ranges for my Businesses if this is at all necessary.

    Again, thanks for your help!

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: INDEX/MATCH across several worksheets

    Do you mean that if you added say in F5, Business 2 to coincide with the Business 2 tab, then only get the value pertaining to that business?

    If so, try:

    =INDEX(INDIRECT("'"&F5&"'!C4:N17"),MATCH(B5,INDIRECT("'"&F5&"'!B4:B17"),0),MATCH(D5,INDIRECT("'"&F5&"'!C13:N13"),0))

    where F5 contains the Business name (with a corresponding equivalent sheetname)

  7. #7
    Registered User
    Join Date
    04-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: INDEX/MATCH across several worksheets

    This works just perfect. I cannot thank you enough for this!

    Can you quickly explain the following part of the INDIRECT function:

    INDIRECT("'"&F5&"'!C4:N17") --> F5 refers to the Business name in my Business colum. Got that, but C4:N17 Is this a range? It looks like you are defining "anchor points".

    Would be great if you can explain.

    Many thanks!

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: INDEX/MATCH across several worksheets

    That is the range of values in each sheet.... you can expand that if you want to the largest range of any of those sheets

  9. #9
    Registered User
    Join Date
    04-24-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: INDEX/MATCH across several worksheets

    You are the man. Thanks very much!

  10. #10
    Registered User
    Join Date
    11-24-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    4

    Re: INDEX/MATCH across several worksheets

    Hi

    I have come across this thread trying to google for an answer and I think this will answer what I need to achieve. But I cannot get the formula quite correct as it is returning an error message.

    From the screenshoots attached you will see that I have a template that gets filled in with the details of the license. This is the one where the company name is V at the top. There can be upto 30 of these templates in the workbook all in the same layout. I am trying to get the data into the summary sheet so that it can be viewed easily. The summary sheet is the one with coloured titles.

    So for example in e3 in the summary sheet is "24". I would like this to be populated from the info entered into c7 in the licence sheet.

    I hope that this makes sense and thanks for advance with any info.

    Summary.JPG
    V Licence.JPG

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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