Excel Summary Sheet

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

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?
 
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)
 
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. :)
 

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.
 
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).
 
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. :)
 
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.
 

Attachments

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
 

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)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top