Results 1 to 10 of 10

Thread: automation

  1. #1
    Join Date
    Nov 2006
    Posts
    67

    Unanswered: automation

    I have a spreadsheet that contains information about a lot of customers. All customers invest in the same businesses and the same products. What I want to do is to create an expression or function that would insert a new row under each businesses and sum up all the values. (applies to the entire spread sheet). Anyone know how to do this?

    Thanks in advance.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    You could use SUBTOTALS. Go to Data > SubTotals and then set up the appropriate columsn that you want summed.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by shades
    You could use SUBTOTALS. Go to Data > SubTotals and then set up the appropriate columsn that you want summed.
    I tried that. I got zeros for my columns. Although, it is all zeros, it gave me a total (zeros) for the customer, but I want more specific, I want the totals of each business investments.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Can you provide a small sample of the data and layout?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Another possibility is to use Pivot Tables. But use a dynamic named range to refer to the underlying data and in the Pivot Wizard, use that defined name. Then you can hit the Pivot refresh button and it will automatically apply changes.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by shades
    Another possibility is to use Pivot Tables. But use a dynamic named range to refer to the underlying data and in the Pivot Wizard, use that defined name. Then you can hit the Pivot refresh button and it will automatically apply changes.
    I am still new to both access and excel. I am using this excel file to update an access report. In other word, I linked the excel file to the database. Would creating a pivot table(s), complicate things? If I recall correctly, pivot table has some limitations or restrictions, like it is not updateable.

    Sample Data:
    Businesses Products Value
    Business 1
    Products 1
    a 5
    b 4
    c 5
    automated sub total here
    Products 2
    a 7
    b 5
    c 3
    automated sub total here
    grand total here

    Business 2 Products 1
    a 4
    b 45
    c 5
    automated sub total here
    Products 2
    a 45
    b 45
    c 223
    automated sub total here
    grand total here
    .

    There are many businesses, and many products under each business. I want an automated sum for products, and also a grand sum for the entire business.

    Sorry bout the formatting
    Last edited by tialongz; 12-13-06 at 11:05.

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    No, Pivot Table is updateable. However, you need to use the dynamic named range so that when you do click the Refresh button, it will include new data rows/columns.

    Name: MyPivot

    Refers to (box at bottom of dialog window):

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

    This will expand as your data is added. Then in the Pivot Table wizard, when it asks for the range put this in:

    MyPivot


    ===============

    Alternative:

    Does it have to be under each item? If you want a separate worksheet for the summary, then you can use SUMIF (for one condition) and SUMPRODUCT (for two conditions).

    If you provide a small Excel file sample (attach the file through the "Manage attachments"), I can show you how to do that.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by shades
    No, Pivot Table is updateable. However, you need to use the dynamic named range so that when you do click the Refresh button, it will include new data rows/columns.

    Name: MyPivot

    Refers to (box at bottom of dialog window):

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

    This will expand as your data is added. Then in the Pivot Table wizard, when it asks for the range put this in:

    MyPivot

    ===============

    Alternative:

    Does it have to be under each item? If you want a separate worksheet for the summary, then you can use SUMIF (for one condition) and SUMPRODUCT (for two conditions).

    If you provide a small Excel file sample (attach the file through the "Manage attachments"), I can show you how to do that.
    Due to circumstances I cannot upload the file nor a screenshot. But if you could go to

    http://www.dbforums.com/showthread.php?t=1612438

    The last message, I attached some screen shot. If you look at the 3rd page, i think that's the screen shot of the excel file.

    I need to sum up all the products for each business. (both column F and G). The sum should appear after the last product for each business.

    This is a just randomed generated data with the right format. The actual data, for each client - the businesses and products are the same.

    Instead of, manually inserting a new row after each business, and under the right column ( F or G) and do =SUM(....). I want to automated.

    I am working with like a huge file.

    Not sure if this helpful, on the actual file, all the clients have like 40 rows. first client starts at row2, 2nd client start at row 42 .. and onwards. The first business listed under the first client starts let's say row 3, the first business listed under the 2nd client starts row 43. I dont know .. .like you create a new criteria, where every certain (40 in this case) rows... insert a new row..and under specific columns...do a summation of the 4 (depending on the number of products) rows above..for example.

    Thanks in advance.

  9. #9
    Join Date
    Oct 2003
    Posts
    1,091
    Still not sure about this. Wouldn't be easier to do subtotals in Access?

    Anyway, here are a couple of approaches. Each assume that the data is filled in.

    Fill Data:

    Select cells A3:G28 (in example),
    Edit > Goto ... Special button to select blanks.
    In formula bar, enter =A3 and hit CTRL+ENTER.
    Select the same area, copy and paste special values

    then on the attachment are two options:

    1. Formula using SUMPRODUCT (I1:K4)

    2. Pivot Table, using MyData with dynamic named range set as shown, then using that as basis for Pivot (see screen shot).
    Attached Files Attached Files
    Last edited by shades; 12-13-06 at 15:22.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  10. #10
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by shades
    Still not sure about this. Wouldn't be easier to do subtotals in Access?

    Anyway, here are a couple of approaches. Each assume that the data is filled in.

    Fill Data:

    Select cells A3:G28 (in example),
    Edit > Goto ... Special button to select blanks.
    In formula bar, enter =A3 and hit CTRL+ENTER.
    Select the same area, copy and paste special values


    then on the attachment are two options:

    1. Formula using SUMPRODUCT (I1:K4)

    2. Pivot Table, using MyData with dynamic named range set as shown, then using that as basis for Pivot (see screen shot).
    I followed your fill data instructions, I am getting REF errors in my spaces.

    ----
    Forget about the subtotals for a second. How do you make it...so that after every certain rows, you insert a new row? (for example, insert a new row after every 2 rows, insert a new row after every 20 rows)

    thanks in advance

Posting Permissions

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