I think you can easily make it with a single workbook.
From where in your Master Overview workbook you want to read data into colored areas in UNLOADDOC?
Into 'Master Overview'.xlsx, add a sheet where you can register shipments (the table must contain an unique code for shipment);
Into table from where you want to read data, add a column for shipment code, and make fields in this column Data Validation Lists, so you can select the shipment for table row. NB! You must have all this data in single table!
Add a report sheet p.e. UNLOADDOC or whatever you want to name it. Design some cell to select shipment code. When shipment code is selected, all info available in data entry sheet is read into report (i.e. the report is filled). To get right rows into report, you need a helper column, where rows are numbered, and another one where entries with shipment code selected on report sheet are numbered. On report sheet you calculate the report row number, check that it is <= as max entry number with selected shipment code on data sheed, when yes, then find the row number with same entry number as report row number (SUMIFS() is your friend here), and read according info from data sheet into report field. When you need to fill some additional data before sending the report out, then enter this info into Shipments table and read from there into report.
When you want to send the report as Excel file, create an empty excel file, copy all datarange on report sheet, and use Paste Special to copy values, formats, and column widths (3 copy operations, in such way you avoid coping formulas which don't work in other Excel file, but preserve the current look of report).
When you want to send the report as printed document, then print it as pdf (install some free pdf-writer like CutePDF int your computer for this).
When you get this report back with some additional info filled in, and you want to keep this info in your Excel database, then have according fields in Shipments table (Unloaded by? Comments?) or in your data entry table (Present), and fill in the info from returned report.
When you want to send another shipment out, simply select on report sheet the code for this another shipment. It's all!
Additional recommendations:
Define your tables as Tables (Insert > Table) - when you add new records into table, all formulas, formats, data validations, etc. are added automatically for new rows (formats, formulas, or data validations must be same for whole column for this feature to work);
Add tables where you can register customers and transporters, and use data validation lists to select them into your data entry table - so you avoid typing errors which may affect your data integrity.
|