Registers and Issue Sheets

[De-coi]

My first post here, so apologise if I have posted in the wrong Group.

In Architecture, we use drawing issue sheets and drawing registers, initially
created using tables in Word. This is cumbersome, and not user friendly. I am
currently updating this and moving this across to Excel.

Very basically, I have a sheet with dates and recipients across the top, and
drawing names and numbers down the left. These areas are then printed on
every sheet. When we issue a new drawing, we enter it's revision number into
the table 'between, these two, where the row for the drawing number and the
column for the date and recipient meet. The register continuously expands
horizontally (extra dates added) and vertically (extra drawings added).

However, where we are sitting with only 4 pages in Word, the Excel equivalent
amounts to nine printed pages, even though we manage to squeeze more
information on a page. This is the natural outcome to using a continuous
table, but not ideal.

I would like to know the following:

- Is it possible to create a second worksheet that automatically fills cells
with info from the first worksheet (the register above), when a drawing is
selected in the register for issuing to a recipient. HOW IT WORKS: The user
selects the drawing to be issued, by placing a cross in a particular cell.
This is registered in the second worksheet, and the information related to
that drawing is then duplicated in the second worksheet in the form of a list
(which will include info from 5 separate cells)

- The pages will print from left to right. At some point, we may have a page
full of dates and lists of drawings, but no actual revisions in the table...
Is there a way to set up a worksheet so that it doesn't print any pages that
don't contain text within the table itself (or within a defined area) Eg...
the table is three pages across, and three pages down. when printing it'll
print from left to right, move to the second row, print L to R, and the same
for the third page. In this example, there are no revisions in the middle
(Page 5) or last (Page 9) pages, yet these will be printed, and in my number
of pages, I will see page 5 of 9, and page 9 of 9. I would like to have these
excluded if possible, and the page number references to exclude pages with
empty tables.

I am also open to other suggestions on creating tables spanning across
multiple pages, which are print efficient.

There is one drawback - my knowledge in using Excel is minimal, and I very
seldom move beyond the basic calculations.

TIA

David

 

[Don Guillett]

Sounds more like a project than a specific question

 

[Arvi Laanemets]

Hi

Your table is an extension of long-past paper table, like your Word table
(quite naturally) was. To use all possibilities of Excel (or some another
application), you have to change your way of thinking cardinally. Design
your workbook as database.

You must have a single sheet (p.e. Data) for everyday user entries.
Something like
Date, DrawingNumber, DrawingName, Recipient, ...

When in some column (p.e. Recipient) values may repeat frequently, then you
may consider having a register table for those values on separate sheet. In
data table, you then apply data validation list for this column, with
register table as source (you define a dynamic named range based on register
table for this). Data validation prevents users from entering misspelled
entries too.

With such design, you can now (relatively) easily design any number of
different report sheets, where user determines some conditions (p.e. date
range, or recipient, ..., or any combination of them), and according report
is designed automatically through formulas. And you can add a column Report
to table Data, where user can select something which indicates, that the row
will be reported, and on one report sheet you can have a list of all marked
drawings - ezactly as you wanted.

You have to estimate the number of drawings in month/year. Depending on
amount and character of used formulas, such worksheets may slow down when
number of entries increases (but a couple thousand rows will be OK for
sure). Am best you determine, for which time interval the table ins meant.
When time is over, you SaveAs the table with some meaningful name, and then
clear all data from original table and start anew. (An example: your
workbook is named Drawings.xls, and you decided, that the table is meant for
a years data. On 1st January, you save the current Drawings.xls as
Drawings2006.xls, clear all data from sheet Data in file Drawings.xls
(leaving column headers intact), and you are ready for entries from new
year.)


Arvi Laanemets

 

[De-coi via OfficeKB.com]

Sounds more like a project than a specific question

Indeed, it feels like it!

...When in some column (p.e. Recipient) values may repeat frequently, then you
may consider having a register table for those values on separate sheet. In
data table, you then apply data validation list for this column, with
register table as source (you define a dynamic named range based on register
table for this). Data validation prevents users from entering misspelled
entries too.
[quoted text clipped - 45 lines]

Thanks for that Arvil. With my lack of knowledge, and as Don so rightly
suggests, this has turned into a project, and looking at your outline, I can
understand how.

I do understand in part what you're explaining, but do start feeling out of
my depth when I get to your last paragraph re: estimating the number of
entries in a year. I have no idea on how to even begin setting this up.

Could you direct me to some good resources on putting something like this
together?

 

[Arvi Laanemets]

Hi

Sounds more like a project than a specific question

Indeed, it feels like it!

...When in some column (p.e. Recipient) values may repeat frequently, then
you
may consider having a register table for those values on separate sheet.
In
data table, you then apply data validation list for this column, with
register table as source (you define a dynamic named range based on
register
table for this). Data validation prevents users from entering misspelled
entries too.
[quoted text clipped - 45 lines]

Thanks for that Arvil. With my lack of knowledge, and as Don so rightly
suggests, this has turned into a project, and looking at your outline, I
can
understand how.

I do understand in part what you're explaining, but do start feeling out
of
my depth when I get to your last paragraph re: estimating the number of
entries in a year. I have no idea on how to even begin setting this up.

This don't have anything with design - you simply have to estimate, how long
you can use the database without it slowing too down. P.e. (a rough
estimation) :
- when you know, that average number of new drawings in year is only a
couple hundred, you can use the same workbook for years;
- when you know, that average number of new drawings in year is some
thousand, you can use the same workbook for a year;
- when you know, that average number of new drawings in year ~10000 or more,
you have to clear your workbook monthly.

Could you direct me to some good resources on putting something like this
together?

I have written a couple of examples in various Excel NG's before. Here are
some links I did wind using GOOGLE - maybe you get some ideas from them:
http://www.excelforum.com/showthread.php?threadid=398458
http://www.eggheadcafe.com/forumarchives/excelworksheetfunctions/oct2005/post24350649.asp
http://www.pcreview.co.uk/forums/thread-2210860.php
http://www.pcreview.co.uk/forums/thread-2379471.php
And check my answer in thread 'Data Columns to table' from Andy at
14.11.2006 05:45 too in this NG here too.

 

[De-coi via OfficeKB.com]

Thanks for all the links.

I find find your detailed explanations extremely overwhelming, but I will go
through it all and see what I can come up with. From what I have read in your
examples, I do understand that this can work, but it'll take some time for me
to get my head around it all.

Thanks again

David