Excel workaround for assigning variable dependent items (parent → uneven child mapping) without VBA

Problem

I needed Excel to automatically assign the correct number of dependent items (tables/sections/etc.) to a selected document type, where each parent could require a different number of children.

Example:

MFR1 → Table 1, Table 2 MFR2 → Table 1 MFR3 → Table 1, Table 2, Table 3

Users should NOT manually choose tables or worry about order. No macros allowed (Undo must keep working).

Solution

I built a mapping table listing valid parent–child combinations, then used a running COUNTA pointer to step through the allowed structure automatically.

Mapping sheet example:

Parent_ID | Child_ID MFR1 | TBL1 MFR1 | TBL2 MFR2 | TBL1 MFR3 | TBL1 MFR3 | TBL2 MFR3 | TBL3

Formula pattern

Running pointer:

=COUNTA(B$2:B2)

Return next allowed child item:

=INDEX(Map!B:B, COUNTA(B$2:B2)+1)

(Optional) return parent sequence as well:

=INDEX(Map!A:A, COUNTA(B$2:B2)+1)

This turns Excel into a sequential allocator instead of a validator.

Benefits

• prevents duplicate selections • prevents skipped required items • works with uneven parent-child structures • survives bulk paste operations • preserves Undo (no VBA required) • scales easily by editing only the mapping table • users never manually choose structure

Extra workflow improvements

I paired this with:

dropdown-only IDs (no typing errors) required vs optional vs disabled field states table-count mismatch detection before output sheet protection to prevent formula damage

Use cases

Document generators routing workflows inspection packet builders signature block sequencing equipment issue trackers multi-section report assembly

Summary

Instead of validating user choices after errors happen, this method assigns the next correct structure automatically using COUNTA + INDEX against a mapping table.