+ Reply to Thread
Results 1 to 12 of 12

Consolidate multiple sheets

  1. #1
    Registered User
    Join Date
    09-30-2015
    Location
    Melbourne
    MS-Off Ver
    2007
    Posts
    7

    Consolidate multiple sheets

    Hey guys abit of an excel novice, I have a situation where I need to consolidate questions from 3 tabs (different questions in each) into a consolidated sheet that has all of the questions. I have a unique identifier for each question that I'm assuming needs to be referenced. I have attached a sample of my situation, basically I want to be able to populate the the first sheet.

    Any help would be much appreciated!
    Attached Files Attached Files

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Consolidate multiple sheets

    Hi,

    Welcome to the forum.

    Try the following formula in C2:

    =IFERROR(INDEX(INDIRECT("'"&$B2&"'!$A$1:$E$3"),MATCH($A2,INDIRECT("'"&$B2&"'!$A1:$A3"),0),MATCH(C$1,INDIRECT("'"&$B2&"'!$A$1:$E$1"),0)),"")

    drag this across and down, see the attached file.
    Attached Files Attached Files

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Consolidate multiple sheets

    One way might be to use a basic INDEx-MATCH-MATCH formula

    INDEX('Questions 1'!$B$2:$H$10,MATCH('Consolidated Sheet'!$A2,'Questions 1'!$A$2:$A$10,0),MATCH('Consolidated Sheet'!B$1,'Questions 1'!$B$1:$H$1,0))

    and nest this basic unit multiple times

    iferror(IFERROR(Sheet1 lookup,Sheet2 lookup),Sheet3 lookup)

    etc
    and then add iferror(big formula,"") to remove any #N/A errors.

    Like this??
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Consolidate multiple sheets

    cbatrody's is better - probably you should use it.... as it is quicker to set up.
    Last edited by Glenn Kennedy; 09-30-2015 at 02:50 AM.

  5. #5
    Valued Forum Contributor mangesh.mehendale's Avatar
    Join Date
    06-04-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    510

    Re: Consolidate multiple sheets

    Simply with Vlookup

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Don`t care, take care...

    Regards,
    Mangesh

  6. #6
    Registered User
    Join Date
    09-30-2015
    Location
    Melbourne
    MS-Off Ver
    2007
    Posts
    7

    Re: Consolidate multiple sheets

    Hi cbatrody,

    Thanks!

    I see what you have done there, I now realise that I haven't correctly explained my issue. The formula will need to look into each tab ('Questions 1', 'Questions 2', 'Questions 3') and basically say that for App 1 is there and information relevant to Q1-Q10.

    I have changed some data in the sample (attached) to have the same questions (different answers) in multiple tabs for seperate App's.

    Does that make sense?


  7. #7
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Consolidate multiple sheets

    Hi Simon,

    If you see the attachment that I posted in my previous post (post #2), I have added a helper column (Column B) to identify the sheet names so that it would be easier for you to amend the summary tab as and when you add new sheets. The formula does lookup for matches from different tabs based on the sheet name updated in column B.

    See the attached file. I have slightly modified the formula to cover the whole data range as per the revised file.

    Try the following in C2, drag this across and down the cells.

    =IFERROR(INDEX(INDIRECT("'"&$B2&"'!$A$1:$I$3"),MATCH($A2,INDIRECT("'"&$B2&"'!$A1:$A3"),0),MATCH(C$1,INDIRECT("'"&$B2&"'!$A$1:$I$1"),0)),"")
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Consolidate multiple sheets

    However, if you're prepared to spend a little time setting it up - the INDEX-MATCH or the VLOOKUP-MATCH solutions that were offered by myself and mangesh will do it without helper columns.

  9. #9
    Registered User
    Join Date
    09-30-2015
    Location
    Melbourne
    MS-Off Ver
    2007
    Posts
    7

    Re: Consolidate multiple sheets

    Thankyou cbatrody, that is a huge help!

    Glenn, I would like to take you up on that request if possible. It would be great to not have to use a helper column if possible as it would make it alot easier for someone else to use once I hand this over and they don't have to know which column the data is being pulled from.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Consolidate multiple sheets

    So, take a look at the solution at Post 2. Does it do what you want?

  11. #11
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Consolidate multiple sheets

    See the attached file
    This was taken from one of "Glenn Kennedy" previous posts
    Created name manager "QName" for sheet names and used for below formula in B2 and copied across

    =IFERROR(INDEX(INDIRECT("'"&INDEX(QName,MATCH(1,INDEX(COUNTIF(INDIRECT("'"&QName&"'!1:1"),B$1)*COUNTIF(INDIRECT("'"&QName&"'!A:A"),$A2),0),0))&"'!"&"$b$2:z100"),MATCH($A2,INDIRECT("'"&INDEX(QName,MATCH(1,INDEX(COUNTIF(INDIRECT("'"&QName&"'!1:1"),B$1)*COUNTIF(INDIRECT("'"&QName&"'!A:A"),$A2),0),0))&"'!"&"$a$2:$a$100"),0),MATCH(B$1,INDIRECT("'"&INDEX(QName,MATCH(1,INDEX(COUNTIF(INDIRECT("'"&QName&"'!1:1"),B$1)*COUNTIF(INDIRECT("'"&QName&"'!A:A"),$A2),0),0))&"'!"&"$b$1:z1"),0)),"")
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Consolidate multiple sheets

    Hi Siva... I don't recall ever having come up with that one. I certainly didn't keep a copy. I shall now, though.

+ 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. [SOLVED] Consolidate all sheets across multiple workbooks
    By AndrewSimpson87 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-10-2015, 12:01 PM
  2. Consolidate lists from multiple sheets into one
    By sarahwarah in forum Excel General
    Replies: 5
    Last Post: 10-28-2013, 02:46 AM
  3. [SOLVED] Consolidate Multiple Sheets into Master
    By megmer in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-18-2012, 12:56 PM
  4. [SOLVED] Copy and consolidate from multiple sheets
    By dutch1213 in forum Excel Programming / VBA / Macros
    Replies: 42
    Last Post: 11-06-2012, 02:36 AM
  5. Replies: 1
    Last Post: 06-21-2011, 03:08 PM
  6. Consolidate multiple sheets
    By scottb in forum Excel General
    Replies: 10
    Last Post: 06-16-2010, 05:01 AM
  7. consolidate info from multiple sheets
    By phil1ray in forum Excel General
    Replies: 0
    Last Post: 05-09-2005, 05:52 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