+ Reply to Thread
Results 1 to 14 of 14

How to prepare summary sheet in the same workbook have multiple sheets?

  1. #1
    Registered User
    Join Date
    06-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    44

    How to prepare summary sheet in the same workbook have multiple sheets?

    Hi everybody,

    I am new to this forum & having a problem in preparing a report. My query is as under:

    I am having around 200 sheets in a single file containing data. The format & the type of data is the same. The sequence in which the data is entered is also the same in all the sheets. In short, all the sheets are same except the figures entered are different, obviously. My requirement here is that I want to prepare a report in a single sheet in the same workbook which contains all the data of these 200 sheets. I know that it can be done by pressing "=" & providing reference to that cell in the respective sheet but in my case it would be a very long task. Just to further clarify, each sheet has around 30 cells in which the data is entered & as mentioned before, there are around 200 sheets. So for preparing my report I will have to give reference for around 6000 times.

    I would be grateful if any one of you could show me a way out. Just to let u know I have no idea about VBA.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    Are all workbook on the same folder, or do you just have a single workbook with 200 sheets? Please attach a sample. To attach, go to advance then attachment.

  3. #3
    Registered User
    Join Date
    06-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    Hi AB33. Thanks for looking quickly to my question.

    Just to clarify your doubt, all the 200 sheets are in a single workbook. I have also attached a sample sheet for your reference. The sheet named "Report" is the sheet which i want to prepare. In short I want to arrange the data of all the sheet in Horizontal format but in one single sheet.

    Hope to hear from you soon.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    Try the attached.
    The code works only if the heading on the report sheet match the headings on column B of each sheet
    Attached Files Attached Files

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    Here is a possible way without using macros. I filled in some of the formulas in the top row and copied down. The only thing that changes from column to column is the specific cell on the sheets being referenced. Seeing that all sheets are identical except for the data, this should work.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Registered User
    Join Date
    06-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    purav82,

    Try the attached. It uses VLOOKUP with INDIRECT to lookup the data itself.

    =VLOOKUP(C$1,INDIRECT($B2&"!B4:G60"),3,FALSE)

    I have noticed that all your tables are not the same so a lookup is better for you than trying to reference cells directly.

    Regards

    David
    Attached Files Attached Files
    Last edited by JBeaucaire; 08-14-2013 at 10:57 AM.

  7. #7
    Registered User
    Join Date
    06-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    Thanks David!!!

    Sorry was not able to reply you back. Was tied up in some other work. But will check on your solution & reply you back soon.

    Thanks once again!!!

  8. #8
    Registered User
    Join Date
    06-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    Quote Originally Posted by newdoverman View Post
    Here is a possible way without using macros. I filled in some of the formulas in the top row and copied down. The only thing that changes from column to column is the specific cell on the sheets being referenced. Seeing that all sheets are identical except for the data, this should work.
    Thanks newdoverman!!!

    Sorry was not able to reply you back. Was tied up in some other work. But will check on your solution & reply you back soon.

    Thanks once again!!!

  9. #9
    Registered User
    Join Date
    06-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    Quote Originally Posted by AB33 View Post
    Try the attached.
    The code works only if the heading on the report sheet match the headings on column B of each sheet
    Thanks AB33!!!

    Sorry was not able to reply you back. Was tied up in some other work. But will check on your solution & reply you back soon.

    Thanks once again!!!

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    _________________
    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!)

  11. #11
    Registered User
    Join Date
    06-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    Quote Originally Posted by purav82 View Post
    Hi everybody,

    I am new to this forum & having a problem in preparing a report. My query is as under:

    I am having around 200 sheets in a single file containing data. The format & the type of data is the same. The sequence in which the data is entered is also the same in all the sheets. In short, all the sheets are same except the figures entered are different, obviously. My requirement here is that I want to prepare a report in a single sheet in the same workbook which contains all the data of these 200 sheets. I know that it can be done by pressing "=" & providing reference to that cell in the respective sheet but in my case it would be a very long task. Just to further clarify, each sheet has around 30 cells in which the data is entered & as mentioned before, there are around 200 sheets. So for preparing my report I will have to give reference for around 6000 times.

    I would be grateful if any one of you could show me a way out. Just to let u know I have no idea about VBA.
    Quote Originally Posted by purav82 View Post
    Thanks AB33!!!

    Sorry was not able to reply you back. Was tied up in some other work. But will check on your solution & reply you back soon.

    Thanks once again!!!
    It works!!!
    THANKS AB33!!!

  12. #12
    Registered User
    Join Date
    06-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    Quote Originally Posted by purav82 View Post
    hi everybody,

    i am new to this forum & having a problem in preparing a report. My query is as under:

    I am having around 200 sheets in a single file containing data. The format & the type of data is the same. The sequence in which the data is entered is also the same in all the sheets. In short, all the sheets are same except the figures entered are different, obviously. My requirement here is that i want to prepare a report in a single sheet in the same workbook which contains all the data of these 200 sheets. I know that it can be done by pressing "=" & providing reference to that cell in the respective sheet but in my case it would be a very long task. Just to further clarify, each sheet has around 30 cells in which the data is entered & as mentioned before, there are around 200 sheets. So for preparing my report i will have to give reference for around 6000 times.

    I would be grateful if any one of you could show me a way out. Just to let u know i have no idea about vba.
    Quote Originally Posted by purav82 View Post
    thanks newdoverman!!!

    Sorry was not able to reply you back. Was tied up in some other work. But will check on your solution & reply you back soon.

    Thanks once again!!!
    thanks newdoverman.

  13. #13
    Registered User
    Join Date
    06-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    Quote Originally Posted by purav82 View Post
    hi everybody,

    i am new to this forum & having a problem in preparing a report. My query is as under:

    I am having around 200 sheets in a single file containing data. The format & the type of data is the same. The sequence in which the data is entered is also the same in all the sheets. In short, all the sheets are same except the figures entered are different, obviously. My requirement here is that i want to prepare a report in a single sheet in the same workbook which contains all the data of these 200 sheets. I know that it can be done by pressing "=" & providing reference to that cell in the respective sheet but in my case it would be a very long task. Just to further clarify, each sheet has around 30 cells in which the data is entered & as mentioned before, there are around 200 sheets. So for preparing my report i will have to give reference for around 6000 times.

    I would be grateful if any one of you could show me a way out. Just to let u know i have no idea about vba.
    Quote Originally Posted by purav82 View Post
    thanks david!!!

    Sorry was not able to reply you back. Was tied up in some other work. But will check on your solution & reply you back soon.

    Thanks once again!!!
    thanks david!!!

  14. #14
    Registered User
    Join Date
    06-24-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: How to prepare summary sheet in the same workbook have multiple sheets?

    Quote Originally Posted by purav82 View Post
    Hi everybody,

    I am new to this forum & having a problem in preparing a report. My query is as under:

    I am having around 200 sheets in a single file containing data. The format & the type of data is the same. The sequence in which the data is entered is also the same in all the sheets. In short, all the sheets are same except the figures entered are different, obviously. My requirement here is that I want to prepare a report in a single sheet in the same workbook which contains all the data of these 200 sheets. I know that it can be done by pressing "=" & providing reference to that cell in the respective sheet but in my case it would be a very long task. Just to further clarify, each sheet has around 30 cells in which the data is entered & as mentioned before, there are around 200 sheets. So for preparing my report I will have to give reference for around 6000 times.

    I would be grateful if any one of you could show me a way out. Just to let u know I have no idea about VBA.
    Quote Originally Posted by JBeaucaire View Post
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Was not knowing it. Will mark it as solved. Really helpful members.
    Thanks to all of them & Thanks to you also Jerry.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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