Internship engineering

A Wide Sheet, and Four Frames

A release lookup tool built at AUDI.

Role: Intern, programming of workflows and linking of databases
Classification: Employer engineering
Institution: AUDI AG

What the work actually produced: a sheet too wide to read becoming four frames grouped by subject rather than by column order.

Problem
About 600 rows and 130 columns, searched by eye.
How it was solved
I built a dropdown lookup that writes back to the sheet.
1. Dropdown
2. Lookup
3. Four frames
4. Write back
Result
4 frames
replaced scanning, other departments adopted it
Built with
- Excel VBA
- UserForms
See the evidence on GitHub

Employer engineering / Internship

The answer was already in the sheet. Finding it meant reading across a screen that did not fit on a screen, many times a day, and the column order was an accident of how the sheet had grown.

What it replacedabout 600 rows

A form over the sheet, writing back to the sheet

The loop closes on the same cells it opened. Introducing a second copy of the data would have traded a reading problem for a reconciliation problem, so the sheet stays the record.

This diagram in words

The existing sheet source — Too wide to read across, and already in everyone's hands.
Component chosen gate — From a validated list rather than typed in free text.
Data validation
Row located process — Behind the form, so nobody scrolls sideways to find it.
Excel VBA
Four frames process — Grouped by subject instead of by the order the sheet grew in.
UserForms
Written back sink — To the same cells. There is no second source of truth.
Event-driven programming

01 / 7One sheet, one row per releaseColumns enough that finding the one you need means scrolling sideways past the rest.

Rows: about 600
Columns: about 130
Both approximate because: the case study publishes them that way

A long unbroken run of column ticks standing for a tracking sheet about 130 columns wide and about 600 rows deep, both stated as approximate because the case study states them so. The ticks take a colour each as they are assigned to a subject, still in the order the sheet grew in, and then migrate across the frame into four groups: department and location, part and supplier, cost and drawings, milestones and lead time. Four frames close around them, which is the point of the work - the fields were regrouped rather than reduced, so a form that fits on a screen is the result rather than the method. The six milestone annotations the release data carries are drawn as labelled marks, each carrying its own comment field. The sequence closes on what the internship report does not record: no count of departments, no time saved and no error rate, so the only adoption claim made is the report's own wording, that it was built for a single department and subsequently enlarged to accommodate numerous departments.

Read the full background

Problem

Why this work exists

One spreadsheet carried the development release state for a department's components: about 600 rows and 130 or more columns. People wanted one record and the sheet made them look at all of them, repeatedly, as part of their job. Two markets carried different fields, so the same question had two shapes.

Contribution

What I can claim

Built the input and output masks: a dropdown that fills a form from the row behind it, fields regrouped by subject rather than by column order, create, update and delete written straight back to the source sheet, milestone annotation, one interface per market, and buttons wired through to the existing release system so the tool's view and the company's view could not drift apart.

Method

Changing the shape of the work, not the amount

The tracking already existed, in a spreadsheet wide enough that finding the right cell was the job.

The column order is an accident of how the sheet grew. The grouping is what a reader actually wants, and choosing it was the work.

Start
From the sheet people already had, rather than from a new system.
Choose
The user picks a component from a list instead of locating a row.
Group
The remaining fields are gathered into frames by subject, so each screen asks about one thing.
Write
Updates go back to the same sheet.
Spread
It was taken up beyond the department it was written for.
What this does not show
The tool is not public and cannot be. What is public is the problem, the design and the figures, drawn by a script from the internship report rather than captured from the application - so a reader can check the argument but not the artefact. No time saving is claimed, because none was measured.

How it was built

System

Workflow and decisions

The workbook held the forms, the modules and the data sheet, and the existing release system sat beside it with a link running between the two. Nothing was duplicated: the sheet other people read stayed the sheet the tool wrote to.

1Open on the sheet people already had
2Pick the component from a list
3Look up its row behind the form
4Fill frames grouped by subject
5Write back to the same sheet

Excel VBA
UserForms
Event-driven programming
Data validation

Evidence

What is actually versioned

What it replacedabout 600 rows

And 130 or more columns, in one sheet, read by scanning the grid by eye. Both figures are approximate in the internship report and are published that way.

What it produced4 frames

department and location, part and supplier, cost and drawings, milestones and lead time. The column order is an accident of how the sheet grew; choosing the grouping was the work, and drawing the form was an afternoon of it.

Taken up beyond the firstone department, then others

The internship report's own wording is that the interface was built for a single department and subsequently enlarged to accommodate numerous departments. It records no count, no time saved and no error rate, and none is claimed here.

Two markets2 interfaces

Over one workbook, because the two markets ask for different fields. Milestones X, P, B, K-AE, BMG, K are annotated on the release data, each with its own comment.

How the work is checked

Quality controls

How the work is checked

The sheet stayed the source of truth, so the tool never became a second copy of it
It lived in the workbook people already had open, rather than being a second place to look
Write-back was in from the start: the people reading the sheet were the people changing it, and a read-only viewer would have been abandoned
The link to the existing release system was wired through rather than reimplemented
The public case study carries no employer source code, no internal data, no part numbers, no suppliers, no real field values, no screenshots and no colleagues' names

Limitations

Where the evidence stops

The tool itself is not public and cannot be. What is public is the problem, the design and the figures, drawn by a script from the internship report rather than captured from the application
The report records no adoption count, no time saved and no error rate, so none of the three is published
The row and column figures are approximate in the source and are not measurements
The wireframe is drawn from the layout the report describes, and no field on it carries a value

What this changed in my practice

The interface was the smallest part. Deciding which fields belong beside which others, and which of the columns nobody actually reads, took the rest of it. The hardest piece was connecting to the system that already existed, which is also the piece that made it worth building: a tool that keeps its own copy of the truth is a second source of truth, which is the problem it was supposed to solve.

Next evidence milestone: Nothing. The internship ended in June 2023 and the case study is a record of it rather than work in progress.

Source and documentation Discuss relevant work