+ Reply to Thread
Results 1 to 11 of 11

Compilation Work Sheet

  1. #1
    Registered User
    Join Date
    12-23-2003
    Posts
    61

    Compilation Work Sheet

    I have a workbook with about 12 worksheets for each of the various vendors I use.
    Each sheet has a column for date-description of items-cost if item-and a column for balance owed.

    My question is:
    I'd like to create a sumary or compilation worksheet which I can designate one cell per vendor worksheet.
    These cells will show the balance owed from each vendor.
    Say I have 12 worksheets, then I'll have 12 cells on the Compilation worksheet.
    One for each worksheet.

    Thank you.
    Last edited by LS Flowers; 12-02-2008 at 04:22 PM.
    coffee man

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Have a look at SUMIF.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-23-2003
    Posts
    61
    Thank you for your reply, but I don't understand how to identify the last cell in the balance column which has a number in it. This balance may fluctuate daily as new rows (cells) are added thru the course of activity.
    Let's say my column A is date of purchase starting with earliest date in A-1 and going down the column A each time I purchase.
    Column D will be the balance column.
    If last purchase is in say Row 10, and D-10 is the balance owed, then how do I specify D-10 on the compilation sheet cell, and, have it automatically change to D-11 when the next purchase/credit is posted?
    Each purchase or credit will be on a seperate row below the last used.

    Thank you for your advice.

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    To adapt to the length of ranges, you can use dynamic ranges.
    It is all explained here

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    if you name the ranges for use in sumif they are dynamic
    so say you have a list of vendors in b1 through to b10
    go insert/ name /define type a name for the range lets say vendor
    click in the refers to box then click cell b1 and highlite down to b10
    so it says (depending on worksheet)something like
    =Sheet1!$B$1:$B$10
    clickadd
    now do the same for the column whers sales are say column d1:d10
    go insert/ name /define type a name for the range lets say sales
    =Sheet1!$d$1:$d$10
    now in d11 put =sum(sales) and all in range sales will be totaled
    in another cell put =SUMIF(vendor,F1,sales)
    now if you type the name of vendor in f1 it will return all sales total for that vendor only
    now the good bit insert a row so d11 moves down and becomes d12
    the range will automatically change. if you look at your defined ranges you (use insert/name /define and click on a name the new range will be displayed just click close to exit without changing anything)will see they have changed to =Sheet1!$D$1:$BD$11 and =Sheet1!$B$1:$B$11
    and the formula inow in d12 will total d1:d11
    and the sum if will still work from criteria in f1 including the added row.

  6. #6
    Registered User
    Join Date
    12-23-2003
    Posts
    61
    Thank you for your reply but I'm having no success.
    My first balance begins in cell (F5) and goes on down the column to a point not yet reached.
    I'm needing the last balance shown in that column whether it be (F5), (F10), or (F150).
    I've tried the formula but I'm getting #REF and also VALUE.
    Sorry, but I don't understand what I'm doing here.
    Thanks again for your help.

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    post a workbook with what you'd like to see

  8. #8
    Registered User
    Join Date
    12-23-2003
    Posts
    61
    This is a mock up workbook but it's basically what I want.
    Thanks again for your help and patience.
    Attached Files Attached Files

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    try like this balance in col c summary sheet must be formated as number(try general and see what you get when 0.0 owed lol)
    Attached Files Attached Files
    Last edited by martindwilson; 12-01-2008 at 08:43 PM.

  10. #10
    Registered User
    Join Date
    12-23-2003
    Posts
    61
    EXCELLENT !!!!!!!!!
    Thank you very much.
    That solved the problem.
    Merry Christmas to you.

  11. #11
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Can you now kindly mark your thread as Solved?

    Edit your original post
    Click Go Advanced
    Select [Solved] from the drop down where it says [No Prefix]
    Click Submit.

+ 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