1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table with 2 sheets

Discussion in 'Ask an Excel Question' started by Veeru106, Sep 12, 2018.

  1. Veeru106

    Veeru106 Member

    Messages:
    310
    Hi,

    i am looking for a solution , where i have 2 sheets of data and i want to create single pivot table...2 sheets data is same except month....first sheet is till jan to oct and 2nd sheet is for nov
    i know there is option to consolidate sheets in pivot table and i am able to create it but i need mutiple option to be in filter category in pivot, which i am not able to do...

    untill now i have created pivot table (sheet attached) but i want Area,category and region in filters and common name in rows , count of ACT1 in values and month in columns.

    Any suggestion appreciated.
    Thanks

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,923
    Version of Excel? Available tool for combining multiple sheets will differ based on it.
  3. Veeru106

    Veeru106 Member

    Messages:
    310
    Hi,
    Version is 2013
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,923
    Thomas Kuriakose likes this.
  5. Topaazy

    Topaazy New Member

    Messages:
    2
    Hi, pivot tables do not support multiple tabs. You can use a QUERY formula instead though, and construct your dataset as a vertical array, for example
    =QUERY({StaffSheet1!A:Z;StaffSheet2!A:Z},"select Col1,Col2")
    You can add all 18 tabs between the { } and separate each range with ;
    If you need help with this, please Share an example sheet wash containing dummy data and paste a link here along with a description of how you want it to work
  6. Veeru106

    Veeru106 Member

    Messages:
    310
    Hi Chihiro....i dont have powe query installed in my machine...what can i see is powerpivot,powermap and powerview.....

    I tired to run a macro for the same link, which you have provided above but it is only creating a another sheet without making any pivot from 2 sheets....

    Topaazy....is the forumula you have used starting from Query is again can be used in powerquery......please explain..thanks
  7. Veeru106

    Veeru106 Member

    Messages:
    310
    And why i need is because my first sheet rows are filled up till 1048576 and my data is till jan to july and for Aug month data i need another sheet and now i want want to create pivot from these 2 sheets...
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,923
    Hmm? If you have PowerPivot, you should have PowerQuery available. Check if you have "Get & Transform Data" in Data tab. If you do, you've got it.

    If not, you'll need to adopt the code, to suite your need.

    It will look like it added new sheet without anything. But if you select B4 of newly added sheet. PivotTable Fields will appear.

    It is left that way, to leave design of the pivot up to the user.
  9. Veeru106

    Veeru106 Member

    Messages:
    310
    Yes i have Get & Transform Data in Data tab....attached doc for your reference..

    Attached Files:

  10. Veeru106

    Veeru106 Member

    Messages:
    310
    yes when i click on From table/range...it opened query editor....it seems just like a power BI....
  11. Veeru106

    Veeru106 Member

    Messages:
    310
    Code given in above link is working file to file attached there...but when i try to run on my sample sheet it gives error "User defined type not defined"...can you plz look into this.....thanks

    Attached Files:

  12. Veeru106

    Veeru106 Member

    Messages:
    310
    Hi


    After tried and testing several times, it is now giving something else error “Multiple step operation generated errors. Check each status value.”


    This error is in Record set code and on “rs.Fields(col - 1) = data(index, col)”


    What would be the reason for it?

Share This Page