Good morning, all!



Normally, I would create a Database Program for this type of project, but - here we are.

BACKGROUND - We are an aircraft company that modifies existing aircraft. Each part of a modification is broken into individual MODULEs (MOD) following the aircraft Drawing Tree (system/included components/airframe location/etc.), each containing the numerous TASKS (step-by-step procedures, called TASK CARDS) required to accomplish each MOD. We currently have 9 aircraft in work, each with its own deviations to the overall MOD/TASK CARD structure.

PROBLEM - I have already created an Excel Workbook we use to track the status of all TASK CARDs assigned to each aircraft. The structure is as follows:

- 1 Workbook, containing 9 tabs (sheets), one for each aircraft
Each sheet is one table, made up of:
- 12 columns (Header), identifying the intended data for the column
- 83 bundled groups, of various quantities of rows, each representing one MOD and all the TASK CARDS for it

BUT - each MOD (bundled group) requires a 'MOD INFO' row (Sub-Header) with the MODULE NUMBER and TITLE to separate the TASK CARDS by MODULE.

As you can imagine, this has presented significant formatting clashes in 'single' large table form, as well as usability issues.

GOAL - To develop a more stable way to present each MOD (as its own Table?), where the data of the entire sheet (all MOD Tables) can still be filtered by the main HEADER row.

SO FAR - Using our current overall appearance and layout (everyone likes it), I have set each MOD as its own table, named to reflect that MOD (i.e., MOD_5_FJ), and have tried to then select all the MOD Tables and the HEADER ROW to then create a single table consisting of all the MOD Tables so I could use the filter row - hah, no such luck.

I am open to suggestions, and questions. I have included a sample of the current and my new worksheets, and an explanatory screen shot.

Thanks, in advance!

SnakeDoctor

Attached Images

Single_Table_Format.jpg (149.1 KB, 14 views)

Attached Files

	NEW_Tally_TEST1.xlsm (135.5 KB, 2 views)
	CURRENT_TALLYxlsx.xlsx (262.3 KB, 1 views)

My advice is - have 2 data entry sheets with a single table on both of them.
1. a sheet with table, where all Mod's are registered;
2. a sheet with table, where all WorkCards for all Mods are registered - with a field for MOD in every row.

And then you need a report sheet, where at top of it user can select Mod, and the table of work cards for this Mod is displayed - either using an ODBC query from Work Cards table filtered by selected Mod, or using formulas which read matching rows of data from Work Cards table.

And you of-course can have any number of other report sheets to get any other info you need.

The bonus of such design is, you can add new mods or new work cards at any time there will be a need, and no redesign of workbook is required!

ArviLaanemets, thank you for your assistance!

This solution is exactly how I would set this up in MSAccess, a data entry form with each entry specified for Task Card#, MOD, and Aircraft.

Would this work for 9 different sheets (tabs)? Each aircraft has its own unique features and quirks, so not every Task Card is released to every aircraft.

Also, there are numerous groups that reference and/or mine data from this workbook and the 'full sheet by aircraft' is their preferred view.

What do you think?

Then you need an additional registry table for all aircrafts, and a field for some aircraft id in workcards table. Workcard must be specified by aircraft and mod.

And then you can create report sheets filtered by Mod, or by Aircraft, or by Mod and Aircract - whatever you need.