Summary Sheet For Drawings Log with Multiple revisons

Hi,

My problem is quite simple but I can't quite find a workaround.
I have to maintain several logs for drawings of various disciplines.The summary for these drawings have to be provided two times a week.
I have attached an extract of the sample log sheet.

My problem is that I cannot create a summary sheet to show all the required information such as Total No.submitted,Hw many with A /B/C status,How many have been received back with status,How many are still not received etc. I have an issue with the revisions for the drawings;the latest revision status has to be counted for a drawing i.e if rev 3 of a drawing is A status,that should be counted.This also creates a problem for me when counting the total no of drawings; I need only the latest revision to be counted for a specific drawing number.

Another problem is that the list gets updated daily;so have to create a dynamic range so that I dont have to update the formulas every now and then.

Also it would be helpful if anyone can guide to use pivot tables for this issue.



Thanks in advance.

Hi Chullan,

I think I have grasped what you are looking for.
See attached file.
The summary table is to the right of the data, in the tan area.

Criteria that are incorporated:

1. Total No. submitted (Column K)
2. How many with A, B, C status (Columns L, M, N)
3. How many have a Received status (Column O)
4. How many still not received (Column P)
5. The latest revision number (Column Q)
6. The latest status letter (Column R).
7. Dynamic! The summary table will update automatically as you enter data. If the data starts to outgrow the summary field just copy the formulas downward.


Regarding using Pivot table. I tried working your different criteria into a Pivot table. While it might be possible, I think it will take several pivot tables to accomplish. If you use pivots, they will have to be updated and refreshed every time you update or add to your data fields.

Take the attached file for a spin. Let me know if you want any changes.

Hope this helpful.

Hi,

Thanks a lot for your effort..
But I find the attachment a bit complex for my level of expertise.

Originally Posted by dflak

The key to the enterprise is Excel Tables. Some of the advantages of Excel tables are that they know how big they are (number of rows and columns) and that they remember formulas and copy them down automatically.

I took your source data and named it Table_Source. I took the summary and named it Table_Summary.

I made a small pivot table from the source data and put it on the parameters page. A pivot table is the quickest and easiest way I know to get a unique list of values. Since this pivot table is reading a table as its source data, it will always reference exactly the right number of rows.

I “overlaid” the pivot table with a named dynamic range: Drawing_Numbers =OFFSET(Parameters!$A$2,0,0,COUNTA(Parameters!$A:$A)-1,1)

I also changed one formula (the one that looks up the drawing title) to show another advantage of tables: natural language syntax: =IFERROR(INDEX(Table_Source,MATCH([@[Drawing Numbers]],Table_Source[Drawing No.],0),3),"").

Otherwise I kept your original formulas.

I used VB code to do the copying.

First I cleared out all the old data (ClearTable). Then I used a pointer (cl) to go down the list of drawing numbers and insert them into the Summary Table. Merely populating the first cell is enough to kick off all the formulas.

To learn more about tables, see this article: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

You may have to change some code. If the summary and source are on different pages you will have to change the page name. Also the column that gets populated by the pointer (Column I in this case), might have to be changed depending on where you move the Summary Table.

You will notice that there isn't a lot of code: Excel Tables did most of the work for me.

Hi,

Thanks a lot for your effort..
But I find the attachment a bit complex for my level of expertise.

Hi Chullan,

I want to help with this. In order to do that I need you to provide a lot more than you have.
I provided what you asked for per the criteria in your initial post using only a sample of the spreadsheet you provided. Which was several hours of work and wasn't easy.

Now you show a picture of what appears to be a working Summary. The initial spreadsheet sample you provided didn't indicate "drawings of various disciplines". Nor did you mention a 14 day overdue criteria.
You stated "I attached the electrical extract yesterday". Extract?
You asking for formulas to provide summary information and all you provide is an Extract!
In order to summarize something you need the whole picture.

Would you be able to construct a building based on just plumbing schematics?

Help me help you. I think this "exercise" is all very doable.

However, in order for me to be able to help with this, you need to provide a sanitized working copy of your spreadsheet.

As for explaining the formulas, I will try.

I really do want to be able to help with this.

Cheers

Originally Posted by southward

Hi Chullan,

I want to help with this. In order to do that I need you to provide a lot more than you have.
I provided what you asked for per the criteria in your initial post using only a sample of the spreadsheet you provided. Which was several hours of work and wasn't easy.

Now you show a picture of what appears to be a working Summary. The initial spreadsheet sample you provided didn't indicate "drawings of various disciplines". Nor did you mention a 14 day overdue criteria.
You stated "I attached the electrical extract yesterday". Extract?
You asking for formulas to provide summary information and all you provide is an Extract!
In order to summarize something you need the whole picture.

Would you be able to construct a building based on just plumbing schematics?

Help me help you. I think this "exercise" is all very doable.

However, in order for me to be able to help with this, you need to provide a sanitized working copy of your spreadsheet.

As for explaining the formulas, I will try.

I really do want to be able to help with this.

Cheers

Hi southward,

I totally agree with your point and apologise for my mistake.

I have attached a copy of the entire workbook.It has 5 sheets;a summary sheet and logs for 4 disciplines namely Structural,Architectural,Mechanical and Electrical

Summary Sheet


Total No - The total number of drawings ( For a drawing number,only the latest revision will be counted and this will also only be used for counting the no of items with a particular status)
Status ( No of A ,B ,C etc.)
Recent Documents No ( No of documents which are submitted and not yet returned ; days elapsed from date of submission < 14)
Overdue ( No of documents which are submitted and not yet returned ; days elapsed from date of submission > 14)


Log Sheets ( 4 disciplines )

The log sheet has subheadings for each building and subheadings ( This is required as sometimes buildingwise logs needs to be generated for management)
The logs maybe updated by 2 or 3 members other than me ( It is better to password protect the formulas )


Please let me know if you need anymore details.

Thanks in advance

Hi Chullan,

Having the whole picture really helps.
I think I have provided what you are looking for.
See attached.

Let me know if you need more or some tweaking.

Cheers

Hi Chullan,

I think I understand what you are looking for. It appears to be an achievable goal.
I'll work on it and let you know as soon as I have something working.

Cheers

Hi Chullan,

I think I understand what you are looking for. It appears to be an achievable goal.
I'll work on it and let you know as soon as I have something working.

Cheers

Originally Posted by southward

Hi Chullan,

I think I understand what you are looking for. It appears to be an achievable goal.
I'll work on it and let you know as soon as I have something working.

Cheers

Thanks southward

Hi Chullan,

I am making progress. I have a plan and structure in place. However, I have run into a snag.

The best way I can figure to make this work the way you want is to use Dynamic Named Ranges.
Dynamic Named Ranges are needed because you expect your data tables to grow on a regular basis.
Dynamic Named Ranges auto adjust to match the length of your data. Without them you would have to update your data array formulas constantly.
If you use formulas that look at an entire column it will significantly increase the computation time for the formula's.

The main problem that arises from using Dynamic Named Ranges on your discipline data sheets is the number of blank cells.
The blank spots represent the separation points that designate the different systems within the disciplines.
Unfortunately, the blank cells cause the Dynamic Named Ranges to come up short. Which causes the formulas not to see all the data.

My solution to this to make a slight modification to the data sheets.
I realize that this might be difficult due to the different disciplines being managed by different departments/personnel.
If you can establish a data structure that is without blanks, at least in the first few columns, it would be much more functional for getting results. It is OK to have blanks in the Rev, Status and Date columns.

Please see the attached file to see the recommended data structure.

If you can accept this type of structure, which is doesn't have any blanks within the first several column, I can provide the discipline summaries and the master summaries that you want.

Hope this makes sense.
Let me know.

Cheers,

Originally Posted by southward

Hi Chullan,

I am making progress. I have a plan and structure in place. However, I have run into a snag.

The best way I can figure to make this work the way you want is to use Dynamic Named Ranges.
Dynamic Named Ranges are needed because you expect your data tables to grow on a regular basis.
Dynamic Named Ranges auto adjust to match the length of your data. Without them you would have to update your data array formulas constantly.
If you use formulas that look at an entire column it will significantly increase the computation time for the formula's.

The main problem that arises from using Dynamic Named Ranges on your discipline data sheets is the number of blank cells.
The blank spots represent the separation points that designate the different systems within the disciplines.
Unfortunately, the blank cells cause the Dynamic Named Ranges to come up short. Which causes the formulas not to see all the data.

My solution to this to make a slight modification to the data sheets.
I realize that this might be difficult due to the different disciplines being managed by different departments/personnel.
If you can establish a data structure that is without blanks, at least in the first few columns, it would be much more functional for getting results. It is OK to have blanks in the Rev, Status and Date columns.

Please see the attached file to see the recommended data structure.

If you can accept this type of structure, which is doesn't have any blanks within the first several column, I can provide the discipline summaries and the master summaries that you want.

Hope this makes sense.
Let me know.

Cheers,

Hi Southward,

This is ok for me with respect to the systems subdivision.
I have only a concern for the grouping by building.
Please check if formulas will work if we add an extra column as shown in the attached pic.

Even if it doesnt work,its not a big deal; ( we can add a column for the building )


Thanks in advance

I think that would be OK. I'll work it into the data sheet. As long as there are several columns such as D and E that don't have blanks in them.

Cheers

Hi Chullan,

I think the attached file meets all of your requests.

There is a summary sheet for each of the disciplines and a master summary.
The summary sheets work with a combination of pivot tables and formulas.
The summary sheets include Conditional Formatting to highlight the dates relative to "recent" submissions (less than 14 days) and "overdue" submissions (greater than 14 days).

The master summary pulls data from the summary sheets.

The "Refresh Summary" button on the master Summary sheet is used when data is added or changed to the "discipline" sheets.

Take it for a spin.

I am working on an Explanation version of the spreadsheet to show how the different formulas work.

Cheers

Hi Chullan,

Hope you have had a chance to work with the latest version of the Drawing Log.

Here is the explanation I promised. I hope it makes sense and is helpful.

Let me know if you have any issues or questions.

Cheers

Originally Posted by southward

Hi Chullan,

Hope you have had a chance to work with the latest version of the Drawing Log.

Here is the explanation I promised. I hope it makes sense and is helpful.

Let me know if you have any issues or questions.

Cheers

Hi Southward,

Thats an excellent work!!
It will take some time for me to get the hang of those formulas...
I am marking the thread as "SOLVED"
Cheers!!!

Hi Chullan,

I am glad I could be of some assistance.
Let me know if you need more help with it.

Cheers