1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Summary Sheet

Discussion in 'Business Applications' started by sarahdiscoballs, Oct 8, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. sarahdiscoballs

    sarahdiscoballs Thread Starter

    Joined:
    Oct 8, 2008
    Messages:
    10
    Hi,

    Hoping you can help. I have an excel spreadsheet which contains several sheet containing company spend (a budget spreadsheet) for each region.

    Each sheet contains the follwoing fields:

    Date of Ordering
    Date Invoice received
    Invoice Number
    Purchase Order Number
    Market
    Description
    Status
    Cost

    I want to create a couple of sheets; one that will list every record in the workbook (displaying the following fields: Date Description Cost Status) and another summary sheet which will display all of the records in the workbook where the status matches "purchase order".

    I have a feeling it's something to do with Lookups or something similar but I'm clueless when it comes to these.

    If anyone can help that will be great.
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    My suggestions (very high), keep all of your data in a single sheet, then just create 2 pivot tables doing what you want. Keep the data on a dynamic named range so you'll never need to adjust your reports ever again (unless you want changes in them). It's easy enough to do. Just add an additional column for Region and combine all data. If you want us to do it, post your file up here. If it's sensitive material, put dummy data in.
     
  3. sarahdiscoballs

    sarahdiscoballs Thread Starter

    Joined:
    Oct 8, 2008
    Messages:
    10
    Hi,

    Thanks for that. Unfortunately having the data in one list is not an option at the moment - the spreadsheet is an inherited one in work, and my line manager is adamant that she wants the format to remain the same (as she created the spreadsheet!)

    Is there any way round this?
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    The first part is easy.

    Create a copy of the first sheet by clicking/dragging/dropping the tab while holding down CTRL.

    Then sequentially copy the data from the other sheets* and paste it below the data of the copied sheet (AKA "append").

    If "several" < 10, then overall a ten minute job. Or am I missing something?

    (*tip: select top left cell to be copied, then while holding down Shift press End -- RightArrow -- End -- DownArrow. This will select all the data if there are no breaks)
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    For the second part, try a helper column on the master (copied) sheet.

    Enter Match in I1 (assuming I've got the columns right). In I2, enter a formula:

    =IF(D2=G2,0,1)

    Copy this down as far as you need, then save.

    Sort by Match (ascending) to put the matching records (Match = 0) at the top. Copy these and paste to a new workbook. Close your main workbook without saving, then reopen to undo the sort. Then move your the sheet from your new workbook to the main workbook and delete the helper column on the master sheet.

    c.5 minutes. :)
     
  6. sarahdiscoballs

    sarahdiscoballs Thread Starter

    Joined:
    Oct 8, 2008
    Messages:
    10
    Sorry to be complete pain;

    Is there a way i can get this to do it automatically? With VB Code or a formula or something?

    There's 12 sheets altogether.
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    For sure there's a VB way. Can you use VB where you are? (at the office, presumably -- some firms disable it).
     
  8. sarahdiscoballs

    sarahdiscoballs Thread Starter

    Joined:
    Oct 8, 2008
    Messages:
    10
    yes we can use VB - our office is kind that way :O)
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    There's something here that looks like it should do the job, but gives me an error. :(

    Give me 10 minutes. :)
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Warning: I only do "quick & dirty" code. :(

    Open the attached and run the test macro. There are some assumptions:

    i) your workbook contains these "regional" sheets only

    ii) each sheet has the same format (8 columns in the same order)

    iii) every record (all sheets) has something for "Date of Ordering" (no gaps)

    If the output sheet Combined is what you're after, we'll do part 2.
     

    Attached Files:

  11. sarahdiscoballs

    sarahdiscoballs Thread Starter

    Joined:
    Oct 8, 2008
    Messages:
    10
    I knew I'd hit a problem eventually... our company firewall wont let me download the file!
     
  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I'll post the code, you insert a VBA module & copy it in.

    Remember I've made assumptions about your layout, so make sure you save your workbook before trying the code. Ideally, try it on a copy of your wb.

    Sub test()
    On Error Resume Next
    Original = ActiveSheet.Name
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Sheets("Combined").Delete
    Application.DisplayAlerts = True
    Sheets.Add
    ActiveSheet.Name = "Combined"
    For Each Sheet In ActiveWorkbook.Sheets
    If Sheet.Name <> "Combined" Then
    Rows2Copy = Sheets(Sheet.Name).Range("A" & Rows.Count).End(xlUp).Row - 1
    Sheets(Sheet.Name).Range("A2").Resize(Rows2Copy, 8).Copy Sheets("Combined").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
    Next Sheet
    Sheets(Original).Range("A1:H1").Copy Sheets("Combined").Range("A1")
    Application.ScreenUpdating = True
    End Sub
     
  13. sarahdiscoballs

    sarahdiscoballs Thread Starter

    Joined:
    Oct 8, 2008
    Messages:
    10
    Hmm... didnt quite work - almost though!

    I should have mentioned that I have some layout cells (just affecting format). If I define the cells I need by name (e.g Russian_expenditure, can I alter the code to just copy these ranges over?

    Sorry to be awkward - I'd upload the dummy spreadsheet but it wont let me (work blocks it)
     
  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    If it's not hyper-sensitive data, then:

    <deleted>
     
  15. sarahdiscoballs

    sarahdiscoballs Thread Starter

    Joined:
    Oct 8, 2008
    Messages:
    10
    I've sent you an email - thanks!
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/757250

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice