1. ## 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. ## 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. ## 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. ## Re: Links to SubTotals (Office XP)

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. ## 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. ## 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. ## 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. ## 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
•