How to pull data from multiple sheets into one master sheet?

hello all...

Thank you in advance for your help. I have searched several times through the boards and can't seem to find an answer to this one.
I have a workbook that has over 50 sheets in is there a way to make a master sheet that has all the data that is sort-able?

i want to pull data start from only cell B4,B5,B6,H42,H43,H44...

check attachment file..

This seems to be easy enough, but I can't figure it out other than manually changing each cell to get it to do what I want.

cheers...

john m

Try this

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010/2013.

Originally Posted by shivya

Hello,

Please find the attachment.
What you need to do is, you have to change sheet name manually only.
Select A2, A3, A4 paste =TRANSPOSE(OFFSET(a!$A$3,MATCH(master!$A$1,a!$A$4:$A$6,0),1,3,1)) and ctrl + shift + enter.

Select D2, D3, D4 paste =TRANSPOSE(OFFSET(a!$A$41,MATCH(master!$D$1,a!$A$42:$A$44,0),7,3,1)) and ctrl +shift +enter.

Please find the attachment.

hi shivya...

how to make your formula if i have 20 sheets...
i mean, how to modify your formula?

Shivya,

When are you expected to be on-line? I need to chat with you regarding XL Formula.

Thanks - Narayan

Try

List of sheet names in column K

in A2

=IFERROR(INDIRECT("'"&INDEX($K$1:$K$20,ROWS($1:1))&"'!B" & COLUMNS($A:A)+3),"")

Copy across to C and down

in D2

=IFERROR(INDIRECT("'"&INDEX($K$1:$K$20,ROWS($1:1))&"'!H" & COLUMNS($A:A)+41),"")

Copy across to F and down

See Sheet1 of attached.

Thank you....

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hello John,

As suggested, I selected Thread Tools from the Menu. But could not see the option SOLVED. Since, I am not the original requester, hence that option is not displaying to me.

Thanks - Narayan

My mistake: only the originator can set thread to SOLVED.