Drawing Register / Issue Sheet in Excel
We use an Excel spreadsheet for our combined drawing register and drawing issue sheet - as do many architectural firms. I have recently given ours a complete overhaul, and moved away from MS Word (using tables), as the Word documents were cumbersome, and user un-friendly, generating too many errors in the office.
The problem with the transfer is that we now have a comprehensive worksheet, that can be added to easily without having to copy entire pages, lists can be sorted quickly and easily, and the file sizes are half what the Word document sizes were.
Only problem is that the number of printed pages for a worksheet end up being considerably more through Excel, than it is for Word, simply because we are adding on horizontally for each new revision issued.
I would like to create an Excel spreadsheet that allows us to have a complete drawing register that doubles as an issue sheet, but that can be kept to as few printed pages as possible. I do not know VBA, and we're using LT 2006 / LT 2007.
I have considered a simple issue sheet, but we need to issue the entire register each time to ensure everyone can see the entire drawing issue list at all times (printed out as PDF when issued). The spreadsheet should require as little user input as possible.
I would like to ask what form your Excel based drawing register / issue sheets take.
Examples will be most welcome!
TIA
2 Attachment(s)
RE: Drawing Register / Issue Sheet in Excel
Thanks for your request Opie.
I have attached the .XLS file as well as a PDF to show what it is meant to look like. This same issue sheet, in Word, takes up only four pages, but that is because each page is a separate entity. The problem with that though is that the distribution list of contacts needs to be copied to every additional page, as must the rest of the table. This causes errors, and if users have to issue drawings on different pages, then they have to add the date for each page, and ensure the distribution is the same. Inevitable, mistakes are made.
This Excel spreadsheet removes this possibility, but increases the amount of printed pages.
Ideally, I would like a complete register, and all that is required, is that the user only needs to 'tick' off the drawings being issued, and to whom they will be issued, and the sheet generates the necessary issue sheet with contacts, drawing names, numbers, scale, size, and revision, but this is too complicated for the recipient's as they would not be able to see past issues (most are too lazy to refer to a separate drawing register, but if this could be created to print over as few pages as possible, it could be added to the issue sheet... If that makes any sense!
RE: Drawing Register / Issue Sheet in Excel
Are you looking for it to print only the drawings that are being issued?
Could this be rotated to landscape?
Can you post an example of the Word document as well?
I'm just trying to figure out all of your parameters that need to be met.
1 Attachment(s)
RE: Drawing Register / Issue Sheet in Excel
I don't mind if only the drawings being issued are printed out, for instance, on a separate drawing issue sheet. I would actually like to be able to set up the drawing register, and then have the user simply place an X into a box on the drawing register, and then that drawing's information is automatically added to the drawing issue sheet.
That starts becoming rather complicated though, and quite a bit beyond my knowledge in setting it up so that the cell in one worksheet (Issue Sheet) reads the information from a cell in another worksheet (Register), which is all set up by simply marking the drawings for issue.
Saying that though, and the way it currently works, is that the recipients need to be able to see the full register.
I have considered landscape, and set to A3 landscape suits me perfectly. It doesn't however suit the office standard, and the preferred option of the design teams, which, unfortunately, is portrait.
I have attached the Word document version of our current register / issue sheet.
1 Attachment(s)
RE: Drawing Register / Issue Sheet in Excel
Wow David that's some use of excel!
We use word, when the issue sheet is full it gets archived and another fresh one is started, you mention word being not user friendly, which I agree with to a certain degree but I've had quite good results by putting the table in a text box, see attached.
I'm not too hot on excel I don't think I'd even know where to start in order to produce what you have done.
RE: Drawing Register / Issue Sheet in Excel
Isn't this starting to sound like a database application? :-(
RE: Drawing Register / Issue Sheet in Excel
Quote:
Originally Posted by Steve.Sanderson(UK)
Wow David that's some use of excel!
Thank you.
Quote:
Originally Posted by Steve.Sanderson(UK)
We use word, when the issue sheet is full it gets archived and another fresh one is started, you mention word being not user friendly, which I agree with to a certain degree but I've had quite good results by putting the table in a text box, see attached..
Very neat sheet. Unfortunately I would still have the same issues with that. If we had to add a drawing to the list, that sits halfway in your first sheet, then all must move down, and then trying to ensure the dates are correct when moving drawings down the list becomes a nightmare when we're approaching three or more pages of drawings, and each sheet is affected by the insertion of a single drawing. Partly the reason why we're needing to change it.
Quote:
Originally Posted by jaberwok
Isn't this starting to sound like a database application? :-(
I have had the same response from an Excel forum I've joined. I may be wrong, but I believe that LT can't be used to form part of a database either (lack of VBA), so it would need to be an Excel database of sorts, I would expect. I've been told about Access, but that means purchasing it for the entire company.
I know how to use Excel, but in a simple way. My knowledge on functions, formulae, and VBA is poor, so if it's indeed a database I need to set up to make this work, then perhaps I should reconsider, and accept either the extra pages, or look at having a completely separate single page issue sheet...
Alternatively, learn how to create a database, but time is not my side with this one.
Anyone know where to go looking for tutorials on setting up a database similar to this....?
:)
RE: Drawing Register / Issue Sheet in Excel
Quote:
Originally Posted by de-co1
Very neat sheet. Unfortunately I would still have the same issues with that. If we had to add a drawing to the list, that sits halfway in your first sheet, then all must move down, and then trying to ensure the dates are correct when moving drawings down the list becomes a nightmare when we're approaching three or more pages of drawings, and each sheet is affected by the insertion of a single drawing. Partly the reason why we're needing to change it.
Yeah I guess its okay for small jobs with only half a dozen drawings or so, but I can see your problem on the larger jobs.
I read somewhere that the sheet set manager or project navigator could be set up using fields to automatically create a drawing register although I haven't looked at this myself to see how effective it is.
RE: Drawing Register / Issue Sheet in Excel
I have had a look at sheet sets and fields in LT. It would be a lot easier if the database could be generated by extracting the data from the title sheet blocks, and would mean the database is kept up to date.
However, it's still that database problem I have, and how to integrate the extracted attribute info Excel using an attribute extraction file, and how to create that database in the first place.
I received a reply in the Excel forum about this, and quite honestly, the response was quite a bit above my head. :?
This was meant to be a simple excercise, and it has turned into a large project - time doesn't allow for that, but I will continue trying to resolve this / learn about Excel databases / get up to speed on attribute extraction in my spare time.
Thanks everyone for your suggestions.
8)
