I've got 2 worksheets, one labelled Data File and one labelled Lookup.
The Lookup sheet is to be maintained by colleagues, as and when they go out
on a visit - they will add customers and/or update the list of products each
customer has access to. The sheet is displayed as per the following:

Row 2:
Company ID / Client No. / Name / Area / Staff Inititals / Product 1 /
Product 2 / Product 3 ...

Row 3 onwards:
1001 / 1234 / Joe Bloggs Ltd / A / PP / Y / (blank) / Y ...
1002 / 2468 / Fred Smith Plc / A / CC / Y / Y / (blank) ...
1003 / 1357 / Davy Jones / B / PP / Y / Y / Y ...

The Data File sheet needs to reflect the data in the Lookup sheet, showing
an individual row for each of the products available to that customer. Take
the details provided in the example above.
The Data File sheet should list 2 entries for Company ID 1001, one for
Product 1 and one for Product 3.
The Data File sheet should list 2 entries for Company ID 1002, one for
Product 1 and one for Product 2.
The Data File sheet should list 3 entries for Company ID 1003, one for
Product 1, one for Product 2 and one for Product 3.

As and when additional products become available to a customer, and another
Y is entered in the relevant row/column on the Lookup sheet, I'd like the
Data File to update accordingly.

How can I go about this? Or is there an easier way to get the outcome I'm
trying to achieve?

Any advice would be appreciated?