+ Reply to Thread
Results 1 to 15 of 15

Dynamic report linked to pivot table

  1. #1
    Registered User
    Join Date
    01-17-2009
    Location
    hyderabad, india
    MS-Off Ver
    Excel 2003
    Posts
    34

    Dynamic report linked to pivot table

    HI,

    My working SCENARIO:

    I AM PREPARING A PRODUCTION SCHEDULE PLAN (MICRO LEVEL). IN OUR FACTORY WE HV THREE TYPES OF MACHINES I.E TYPE-A , TYPE-B and TYPE-C.

    In TYPE-A machine we have 10 numbers
    In TYPE-B machine we have 8 numbers.
    In TYPE-C machine we hv 12 numbers.

    total we hv 30 machines in our factory. when any order is recd, we start allocating the order to particular type of machine depend upon the reqmt. the production start date will be decided based on raw material arrival date and end date is fixed based on delivery date of that order. Number of machines allotted to particular order is depend upon total ord qty of that order and number of days available for production.

    normally this planning is done for next 3 months. attached excel sheet, where the work sheet with name "master", shows the number of machines available for each date till march end.

    all details are given in the excel sheet.

    WHAT I WANT:

    from the master sheet, pivot table is generated in next sheets with name "PIVOT-1" and "PIVOT-2". The SUMMARY Sheet data is linked to PIVOT-2 and the formulas used in this are absolute ranges refering the pivotal table. I need this formulas to be dynamic and when PIVOT-2 is expand or shrink, the range should automatically or dynamically modify itself to give proper result.

    is there any other concept by which i can simplify this process of allocation instead of pivot table or entering master sheet.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-18-2010
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010
    Posts
    79

    Re: Dynamic report linked to pivot table

    Hello murarihyd

    I am trying to return our spreadsheet returned with some ideas on use of DSUM, DMAX and DMIN functions bye-passing your pivot tables

    When and if you receive it, please see Formulae and Criteria areas on your SUMMARY worksheet. I have given the data on your MASTER worksheet the range name 'MyData'.

    I am having trouble making the attachment. I have all the required rights, but the buttons are not working for me.
    David
    Access and Excel Developer | UK

  3. #3
    Registered User
    Join Date
    12-18-2010
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010
    Posts
    79

    Re: Dynamic report linked to pivot table

    There we go ...
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-17-2009
    Location
    hyderabad, india
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Dynamic report linked to pivot table

    in the same formula how can i do the dynamic range selection

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: Dynamic report linked to pivot table

    Hi murarihyd,

    Have you considered Dynamic Named Ranges? This might be just what you need.
    http://www.ozgrid.com/Excel/DynamicRanges.htm or
    http://www.beyondtechnology.com/geeks007.shtml
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Registered User
    Join Date
    01-17-2009
    Location
    hyderabad, india
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Dynamic report linked to pivot table

    I hv tried the dynamic name ranges. Since the range what i am selecting is from pivot table. On refreshing the pivot table, the new range is not selected.

    Pls provide some sug

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dynamic report linked to pivot table

    I think MarvinP's point was more that you create a DNR based off Pivot2 data such that as the Pivot itself expands/contracts so too does the Named Range, eg:

    Please Login or Register  to view this content.
    we assume we're not interested in Grand Total row/column as may not be visible at any given time and that "DATE" appears in A5 at all times.

    You can then reference the Named Range using INDEX as you are presently with the fixed range.

  8. #8
    Registered User
    Join Date
    01-17-2009
    Location
    hyderabad, india
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Dynamic report linked to pivot table

    i hv tried by creating the DNR in excel 2007 using name manager.

    i hv pasted your formula in refer to and i could not see the name range in the name box.

    pls can you guide me further or can you do it in my sheet and update it.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dynamic report linked to pivot table

    I don't know what you mean by "Name Box" but if you're referring to the drop down above A1 it won't appear there.

    Having added the Name as outlined - if you then use:

    Please Login or Register  to view this content.
    what is the result ? #REF! or other ?

  10. #10
    Registered User
    Join Date
    01-17-2009
    Location
    hyderabad, india
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Dynamic report linked to pivot table

    the box above the A1 cell is NAME BOX (WHEN I KEEP THE CURSOR, IT SHOWS THIS NAME).

    WHERE I NEED TO USE THIS CODE.

    PLS GUIDE ME

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dynamic report linked to pivot table

    As outlined the Defined Name just created will not appear in that box but it does exist - hence my request you test the ROWS function as outlined and report back with the result.

    On an aside - do not use CAPS in your general narrative - CAPS are seen as shouting.

    In terms of guidance - I think we're doing the best we can - "spoon feeding" to the extent that you don't do these things for yourself is not going to help you in the long run.
    Far better that you get to grips creating and using these Names/formulae yourself than us simply hand you the finished article.
    If you're looking for the latter then you should be employing consultants.

  12. #12
    Registered User
    Join Date
    01-17-2009
    Location
    hyderabad, india
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Dynamic report linked to pivot table

    sorry for bothering you too much.

    thank you for make me aware about typing in caps.

    i will try and revert you back.

  13. #13
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: Dynamic report linked to pivot table

    Hi,

    When I suggested Dynamic Named Ranges - here is an example.

    On your Summary Sheet you have a formula in E3: of
    Please Login or Register  to view this content.
    You said that this range will expand and the $B$6:$J$58 will get bigger as more rows are added to the Pivot Table.

    If you define a Dynamic Named Range of
    Please Login or Register  to view this content.
    Then in your formula above instead of using the Static Range you replace it with the name and it becomes:
    Please Login or Register  to view this content.
    Now when your pivot table data grows the Dynamic Named Range of Pvt2Data also grows and all your formulas continue to work.

    Looking at some of your CSE formulas, you may need to create a few Dynamic Named Ranges to have them continue to work.

    I hope this helps explain where I was going.
    http://www.ozgrid.com/Excel/advanced-dynamic-ranges.htm for more examples.

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dynamic report linked to pivot table

    FWIW, given the nature of the calculations I'd be wary of using a DNR with a Volatile construct (hence prior INDEX based suggestion)

  15. #15
    Registered User
    Join Date
    01-17-2009
    Location
    hyderabad, india
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Dynamic report linked to pivot table

    HAPPY NEW YEAR to all forum

    i am trying your suggestion. will revert back soon.

+ 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