Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Links to SubTotals (Office XP)

    Hi all,

    I have a workbook which I have created links within the sheets. I notice that whenever I copied a new set of data from another worksheet with sub totals to the first sheet of the linked workbook, the links will broke down because the sub totals vary from week to week in rows but in the same column, as such I will have to links them back again. What can I do to have those links intact.

    Thanks Always, kun

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Links to SubTotals (Office XP)

    I am not sure exactly how you have it set up, but is sounds like you may need to either calculate the values directly (with formulas) as desired from the full data set (instead of pointing to a cell whose location changes) or use some type of lookup to find the cell of interest.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links to SubTotals (Office XP)

    You may want to explore the SUMPRODUCT function and use its ability to create totals directly from tables of data without developoing intervening subtotals. If your data set changes, create a dynamic range name to expand the selection automatically. Additionally, the sumproduct funtion can reach into closed workbooks to get data. I haven't tried that yet. But as an accountant, I have found getting into the SUMPRODUCT function well worth the time.

  4. #4
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links to SubTotals (Office XP)

    Steve and Torquemada,

    Thank you for your response. I am not sure of how can I implement the suggestions given. I have attached a dummy workbook, The first sheet, "Products" is where the processed data ( in another wb ) copied on to it contain sub totals. I have create links from the 2nd sheets onward.
    I face the problem of the links in the SumCharges will breakdown whenever the sub totals are in different rows than the one I attached
    What I like to do it that whenever I copy a set of processed data from another worksheet onto the first sheet of the attachment
    the links will not breakdown and know how to find their respectives sub totals

    Thanks , kun

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Links to SubTotals (Office XP)

    If your question is about SumCharges!D2326 which are linked to cells in the Product sheet, you could use a SUMIF formula. In SumCharges!D23 enter:

    <pre>=SUMIF(Products!$A$9:$A$23,A23,Products!$E$9: $E$23)</pre>


    Copy from SumCharges!D23 to SumCharges!D2426

    You can change the range as appropriate. If the range will expand Dynamic Names could be used to define the range. You could also just use the entire column, but that seems "wasteful of resources":

    <pre>=SUMIF(Products!$A:$A,A23,Products!$E:$E)</pre>


    This method does not even require the use of the subtotals at all, and the data does not even have to be sorted...

    Steve

  6. #6
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links to SubTotals (Office XP)

    Hi Steve,

    It seem to be working, Thank you <img src=/S/bow.gif border=0 alt=bow width=15 height=15> .

    cheers,

  7. #7
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links to SubTotals (Office XP)

    Hi , can you explain how can I use the SUMPRODUCT given the attachment I have posted. Would appreciate if you can spare some time.

    thanks always, kun

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Links to SubTotals (Office XP)

    You do not need to use sumproduct (or other array solutions) unless you have multiple conditions to test for.

    Another solution, if you avoid the subtotal completely and work with the full dataset, is to just use a pivot table to create summary table of stats.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •