Insert Data Headers with same formatting as other Worksheet, based on a drop down list

[murmerer]

Hello All,

I have a dilemna in Excel that I can't seem to figure our a way of solving
I work in a laboratory and have many spreadsheets for different companies data, that I am trying to merge into a single more dynamic spreadsheet with dynamic headers based on
a drop down list. This would eliminate be going back and forth all the time between spreadsheets.

Each company has a three row header above their data. I have put the header data for the companies into a table on Sheet 1.
The real problem is I want the formatting and colours etc to stay as they are now like in Sheet 1, so for one company a cell might be yellow and for another company the same cell might be blue.

I have had a bit of a play around and got up to the point where if I type "Company 1", "Company 2", etc... into the grey cell in cell B2 on Sheet 2, the headers do dynamically change as I want, but I don't know how to dynamically change the formatting of Sheet 2 (based on the company selected in the grey cell on Sheet 2) to match the formatting of the header data on Sheet 1

Also, does anyone know how I can make the grey cell into a drop down list based on the companies listed in column A in sheet 1 without any blanks in it? no matter what i tried the blanks always seem to be in the drop down list

Spreadsheet and screenshots are attached
If anyone has any ideas, thanks in advance

[TMS]

You're not going to be able to copy formatting without VBA. Formulae, however clever, can only reference data … in this case, the text in the headings.

[murmerer]

Thank you for your reply. I assumed this would be the case, perhaps I will have a rethink of my approach. Something like all header data for all the companies next to each other in the first three rows, rather than having it dynamically change. That could work I guess if I can create VBA/buttons to hide/unhide cell ranges based on what I need open at one particular time

[TMS]

If the Company names are present, or can be included in your Table, you might be able to use Conditional Formatting to provide the colours/formats you require.