Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Customer totals in a spreadsheet (Excel XP)

    Hi all,

    Perhaps someone can give me a starting point to go from on this project.

    I do my invoicing in Excel. I have cells with the customer's name, date, total sale etc. It's a nice clean invoice that I've developed over the years.

    When I run my PRINT INVOICE macro one of the things that is done is the total for that invoice, as well as several other line items are copied to a YEAR TO DATE sheet so that I can always see my total sales breakdown up to any give date. This has worked nicely.

    Now I want to do something that is perhaps better suited to be done in something like Quicken, but I happy with the way I have everything else set up in Excel.

    I want to come up with year to date totals for each customer. I'd like to copy the sale date and the invoice total cells into a worksheet that has a row or column for each main customer. Intermittent or one time cutomers could fall into a separate "miscellaneous sale" row or column.

    I know from my YEAR TO DATE sheet how to have totals from my invoice added to a number in an existing cell in that sheet. I'm just not sure where to start in terms of selecting each customers correct columns for my CUSTOMERS TOTALS sheet. I would like to have a separate entry for the date and amount of each sale under the correct customer on each sale, with a total for the year then at the bottom of that column for example. A quick look will tell me how many times a customer has ordered, how much money was involved, and the total for the year.

    As always all your help is deeply appreciated.

    Thanks,
    BH Davis

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Customer totals in a spreadsheet (Excel XP)

    You may be able to do this without copying all that data, by using the Data | SubTotals capabilities, or you could use SUMIF formulas. It would be helpful if you could post a censored (disguise the customer & number data) example of the spreadsheet to show the layout of the source data.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customer totals in a spreadsheet (Excel XP)

    John,

    Thanks for the reply and suggestions. No problem with posting the invoice. Here it is.

    Thanks,
    BH Davis

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Customer totals in a spreadsheet (Excel XP)

    Maybe I misunderstood the original question, but I was looking for a sample of how you keep your customer invoice records that you want to summarize, not the invoice form blank. So I'm probably looking for a sample of the YEAR TO DATE sheet.

    (And I'm about to head out for a few days, I'm sure someone will pick this up.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customer totals in a spreadsheet (Excel XP)

    John,

    Thanks again. Here's the YTD sheet. There's no rush on this.......I've been thinking about for a number of weeks.

    Take care,
    BH Davis

  6. #6
    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: Customer totals in a spreadsheet (Excel XP)

    I am unclear what you start with and what you want the results to be. Could you put some data into it about what you might have as a start and an idea of what you want the results to be?

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customer totals in a spreadsheet (Excel XP)

    Steve,

    Thanks for getting involved here.

    I think if you ignore the earlier attachment of my YTD sheet, and instead just look at the INVOICE attached above and this new CUSTOMER YTD sheet attached here you'll probably get the gist of what I'm trying to do.

    I appreciate your help.

    Thanks,
    BH Davis

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customer totals in a spreadsheet (Excel XP)

    Sorry, I'm afraid I'm not going to be much help in writing cool macros to get the job done. But I wonder if a different approach wouldn't be easier to accomplish what you're wanting.

    What if you took the invoice and pasted via macro to a sheet set up more like a database with 3 columns - one for Name, Date, and Sale. Then have a separate sheet read that data and put it into a pivot table. You would get essentially the same report, but it seems like it would be much easier to accomplish.

    Maybe it's not what you're looking to do...just an idea.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customer totals in a spreadsheet (Excel XP)

    Ban,

    I'm open to anything. I'll do a bit of research to learn about Piovt Tables.

    Thanks,
    BH Davis

  10. #10
    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: Customer totals in a spreadsheet (Excel XP)

    If you are going to go Ban's route, this routine will transfer the data from the invoice sheet to a data sheet in that format (Name, Date, Total).

    <pre>Sub CopyInfo()
    Dim wksData As Worksheet
    Dim wks As Worksheet
    Dim lRow As Long

    'change name as needed
    Set wksData = Worksheets("data")
    Set wks = Worksheets("INVOICE")

    With wksData
    lRow = .Range("a65536").End(xlUp).Row + 1
    .Cells(lRow, 1) = wks.Range("e9").Value
    .Cells(lRow, 2) = wks.Range("I10").Value2
    .Cells(lRow, 3) = wks.Range("K44").Value
    End With
    End Sub</pre>


    The you can create a pivot from this range as needed to summarize the totals.

    Post back if you have additional questions.
    Steve

  11. #11
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customer totals in a spreadsheet (Excel XP)

    Steve,

    Thanks.........I'll try and find some time over the weekend to work with that routine.

    BH Davis

Posting Permissions

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