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
Open up the VBA editor by hitting ALT F11
Please Login or Register to view this content.
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.
Martin
Hi jhon,
If you had a newer version of Excel that had the Power Query tool, you could use it to do this work. See:
https://social.technet.microsoft.com...rum=powerquery
Time to upgrade to a newer version of Excel?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
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.
To attach worksheet Go advanced -> Manage attachments -> Choose file -> Upload
Don't forget to Mention your desired result in the sheet..
There should be sample data only, a lot of data creates confusion.
Thanks & Regards
Shivya
http://excelvbatipsforbeginners.blogspot.in/
Originally Posted by shivya
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
Hello,
You need to change the sheet name manually every time.
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.
To avoid further confusion, I have marked it as such.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks