+ Reply to Thread
Results 1 to 11 of 11

Extract Data from multiple tabs in one workbook to a summary tab

  1. 10-23-2014, 02:21 AM #1
    iceplant
    iceplant is offline
    Registered User
    Join Date
    08-19-2013
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Extract Data from multiple tabs in one workbook to a summary tab

    Hello! First thanks for any help here!!

    I have more than 100 tabs in one workbook. They all format quite similar. What I need to do is to create a summary page to pull some data from each worksheet. I have a sample file attached.


    For example, I have all the tab names listed in the summary page. I need to pull the data on BB row, Q4'14 column in each tab. The issue is that BB row is always in column B but not sure which row it ends at in each tab. Same as CYQ4'14 column. So I need to create a dynamic range to locate the number under BB row and Q4'14 column
    I come up with indirect formula to extract numbers from each tab and use vlookup + match to lookup in a dynamic range, but it turns out not working. Not sure where it is wrong.
    =VLOOKUP(C4,INDIRECT("'"&B5&"'!B:F"),MATCH(B3,INDIRECT("'"&B5&"'!1:100")))

    Could anyone help on this ? If there is any good VBA code, that will be great too.

    Thank you very much for your help!!!!!Really appreciate!!!!


    Summary tab look like this:

    Q4'14
    BB Accrual Payment EB
    Company1
    Company2
    Company3

    Each tab looks like this:


    Q3'14 Q4'14 Q1'15 Q2'15
    BB 180,000
    Accrual - 25,000
    Payment (64,000)
    EB - 141,000 - -
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-23-2014, 02:43 AM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Extract Data from multiple tabs in one workbook to a summary tab

    First, list the Quarters in a range off to the right. We can even use those cells to create a Drop Down list B3.

    Then the formula in C5, which is then copied down and across the table, becomes:

    =VLOOKUP(C$4,INDIRECT("'"&$B5&"'!B:F"),MATCH($B$3,$J$1:$N$1,0),0)
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 10-23-2014, 02:46 AM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Extract Data from multiple tabs in one workbook to a summary tab

    Hi, welcome to the forum

    If you can at least keep your column headings in the same row (or even create a row for them to be in), then you can use this, copied down and across....
    =INDEX(INDIRECT($B5&"!$C$7:$F$100"),MATCH(C$4,INDIRECT($B5&"!$B$7:$B$100"),0),MATCH($B$3,INDIRECT($B5&"!$C$6:$F$6"),0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 10-23-2014, 03:10 AM #4
    apo
    apo is offline
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Extract Data from multiple tabs in one workbook to a summary tab

    Hi..

    Don't forget to add a link to this crosspost: (and any others you may have done)..

    http://www.ozgrid.com/forum/showthread.php?t=191557
    Reply With QuoteRegister To Reply
  5. 10-23-2014, 04:44 PM #5
    iceplant
    iceplant is offline
    Registered User
    Join Date
    08-19-2013
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Extract Data from multiple tabs in one workbook to a summary tab

    I was so excited to see it is working at first. You guys are so smart! However, I noticed that in my acutal file column Q4'14 is not always the second column in the table and the position really depends on the starting quarter....bummer

    I am thinking if I need to do a link in each tab to make the quarter heading all link to row1:1. Then my row range will be always in row A1 something to F1 something. Not sure if that works.

    But any other formular to make this work?

    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 10-23-2014, 04:49 PM #6
    iceplant
    iceplant is offline
    Registered User
    Join Date
    08-19-2013
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Extract Data from multiple tabs in one workbook to a summary tab

    This formular does work but it is the same issue as the above. My column heading is always in column B. (BB/Accrual/payment/EB)But my row heading is not fixed in certain line or column

    Thank you!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-23-2014, 07:36 PM #7
    iceplant
    iceplant is offline
    Registered User
    Join Date
    08-19-2013
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Extract Data from multiple tabs in one workbook to a summary tab

    sorry, I thought these are two different sites....

    Thanks for the reminder
    Reply With QuoteRegister To Reply
  8. 10-23-2014, 07:47 PM #8
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Extract Data from multiple tabs in one workbook to a summary tab

    Crossposting the same question on multiple forums is great for you, the asker, and a horrible chance at a waste of our time, the answerers. I know it's not your intention, but it definitely happens. Our volunteer's time is very precious, and we do not ever want them spending any time trying to solve a puzzle you have posted and solved elsewhere.

    So, when you do it, and we really wish you wouldn't, we require you to provide links TO the other threads so at all times we can see the progress on the topic in all locations.
    Reply With QuoteRegister To Reply
  9. 10-23-2014, 08:46 PM #9
    iceplant
    iceplant is offline
    Registered User
    Join Date
    08-19-2013
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Extract Data from multiple tabs in one workbook to a summary tab

    Sorry that I thought these are two different sites casue I always find my answer from these two places. Will not do in the future. Thanks for letting me know

    Any comments on my previous comments? Really appreciate your time and help~~~
    Reply With QuoteRegister To Reply
  10. 10-23-2014, 08:47 PM #10
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Extract Data from multiple tabs in one workbook to a summary tab

    We are different sites, but read what Jerry said regarding x-posting.

    I think this will work for you, copied down and across...
    =INDEX(INDIRECT($B5&"!C7:F100"),MATCH(C$4,INDIRECT($B5&"!B7:B100"),0),
    MATCH($B$3,INDIRECT($B5&"!$C"&MATCH($C$4,INDIRECT($B5&"!B:B"),0)-1&":$F"&MATCH($C$4,INDIRECT($B5&"!B:B"),0)-1),0))
    Reply With QuoteRegister To Reply
  11. 10-24-2014, 12:36 PM #11
    iceplant
    iceplant is offline
    Registered User
    Join Date
    08-19-2013
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Extract Data from multiple tabs in one workbook to a summary tab

    Thanks for all the help and your precious time. Sorry that I don't know the rule and bring the trouble for your guys!

    The problem is solved here.
    http://www.ozgrid.com/forum/showthre...=191557&page=2
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Summing in one summary tab data from multiple tabs
    By loulite in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-15-2014, 10:31 AM
  2. [SOLVED] Populating data into a Summary tab using multiple data tabs
    By S1E in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-14-2013, 09:32 PM
  3. [SOLVED] Extract data from a workbook with multiple tabs, into a workbook with corresponding tabs
    By krackaberr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2013, 11:54 AM
  4. Extract data from multiple worksheets in multiple workbooks into one master summary file
    By awcwa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-05-2013, 02:20 AM
  5. Combine data multiple tabs into one Summary Tab
    By rorybecers in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2011, 05:59 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1