Excel: SUM without Pivots? 2

[toonaway]

Hi all,

I was wondering if some kind soul could point me in the right direction. I am somewhat of an Excel newbie and have come to a halt on a Workbook.

The Workbook has, amongst others, three key Worksheets.

Worksheet 1: Job Data
This has columns that capture project number, quoted duration, value etc.
A project number can have several jobs.

Worksheet 2: Actual Data
This worksheet records the actual duration of completed work, the first appointment and last appointment etc.
(It is not manageable for it to be in Worksheet 1)

Worksheet 3: Reporting
This where I want to report on Job Data vs Actual Data in a variety of forms, including SUMs of values.

The key thing here is that all Worksheets refer to a project by project number, so I am only interested in
totals for a project number(SUM of values etc).

If this has made any sense at all, and somebody is still awake; I am seeking suggestions on how I apply SUMs on values stored against project numbers and link the three worksheets by project number in the process so that I can add data against the project number in Worksheet 3.

I thought about adding a further worksheet using pivot tables to perform the SUMs, this would be great if I
didn't then want to use the project number and SUM in the reporting Worksheet.

Any help gladly received.

tOOnaway

 

[klmorgan]

I'm not clear what you want to add in sheet three.

If you are trying to get a report in the reporting sheet of a single job and hours values you could use an Advanced filter to sort the time sheets for a single job and perform totals on that. You can use vlookups to find related items across sheets

If you want current totals for all jobs then a Pivot table should do the job that is if the project number appears in the rows.

Regards

Keith

 

[toonaway]

Thanks Keith,

I am looking to provide a summary in Worksheet 3 for all projects that basically SUMs duration, value etc by Project Number to compare between quotes and actuals.

My envisaged columns are:

Project#, Customer,Quoted Value, Total Value,Difference, % Difference, Quoted Duration, Actual Duration, % Difference Duration, Surplus/Lost Revenue

So I would be essentially reporting on all projects, but not at "Job level".

Any clearer?

tOOnaway

 

[klmorgan]

How about:

Pivot table over Quote Table giving Sum of Quoted Value and Sum of Quoted Duration Sorted on Projet Number

Pivot table over Actual Table giving Sum of Quoted Value and Sum of Actual Duration Sorted on Project Number

Name the ranges of each Say "Quote" & Actual

Report = List of Job Numbers With Vlookup to Quote Table
and Actual Table

Also Link to a Customer table

and perform calcs of % etc on this data

You would need to flag which Projects were completed so that incomplete jobs were either excluded or at leats identified.

Regards

Keith

 

[toonaway]

Keith,

Thanks for spending so much of your time on this for me.

Your last post makes sense to me (an increasingly rare occurrence these days) and I will give it a try.

I do have a field for completed projects as you suggest.

Just one question, having used VLOOKUP to populate the reporting worksheet, can I then add a comments column to that worksheet that will always tie up with the Project Number, even if somebody includes a new Project Number in the quote table?

Our project numbers are of the format XX0000 where "XX" is the initials of the Project Manager and 0000 is a sequential number. My concern is that a new project number for the Project Manager "Alan Baker" would essentially bump all project numbers down a row, leaving the comments referring to the wrong project number.

Thanks again,

tOOnaway

 

[klmorgan]

Thats Ok

You could add comments there but would need a macro to move them into the comments file.

The simplest way would be to have a comments file made up of 3 fields perhaps:

Project No Date Comment


The validate the Project number using Data/Validation from your Project File (Providing there are no Duplications)

And then use an Advanced Filter to find the comments for a particular project.

The more able guys in here may be able to find a better way but I'm only a few steps further ahead than you!

Advanced Filters, Pivot tables and functions like DCOUNT, DSUM and simple macros figure greatly in what I produce and I have to keep it simple!

Regards

Keith

 

[KenWright]

Assuming you have already included a unique list of Project Numbers in sheet 3, then you should simply be able to use something like the following to be able to summarise data from Sheet 1:-

Sheet 1
A B C D
1 Project Job No Duration (Wks) value
2 1234 111 12 100,000
3 2345 112 15 100,000
4 1234 113 11 100,000
5 4567 114 18 100,000
6 5678 115 16 100,000



Sheet 3
A B
1 Project
2 1234 =SUMPRODUCT((Sheet1!$A$2:$A$20=Sheet3!A1)*(Sheet1!E2:E20))
3 2345
4 3456
5 4567
6 5678


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[toonaway]

Keith

Thanks for your help.

I only used Pivot Tables for the first time this week, and I already wonder how I lived without them !

I wonder if there is a single person who actually knows how to use all of Excel's functionality !

Thanks again, I shall leave you in peace now and implement your suggestions.

Regards,

Jonathan

tOOnaway

 

[KenWright]

<grrrrr> All I did was hit Preview and all it did was post prematurely. I had meant to change the last reference to be Column D not E, and then say you need to copy the formula down.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[klmorgan]

By the way it took me ages to find how to put a sort on a field. It's Field Settings/Advanced

You will probably need this


Regards

Keith