# Consolidate multiple sheets

1. ## 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!

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

3. ## 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??

4. ## Re: Consolidate multiple sheets

cbatrody's is better - probably you should use it.... as it is quicker to set up.

5. ## Re: Consolidate multiple sheets

Simply with Vlookup

Formula:
`Please Login or Register  to view this content.`

6. ## 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. ## 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)),"")

8. ## 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. ## 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. ## Re: Consolidate multiple sheets

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

11. ## 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.``

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

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

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