Survey in excel

Dear all,

I am currently facing a mind-breaking excel challenge and I really hope someone can help me out.
I have attached an excel file to define the issue as clear as possible. The idea is to create a survey among some companies (in this case companies A-Z) and ask them how many people they employ per staff department.
The challenge I face now is two-fold:

1) In the second sheet 'employees' I want the manager to provide some information. I have locked all cells except the light green ones, which are the ones he should fill in.
First the manager should select the firm he represents in cell 'D2'. Afterwards I want him to fill in the number of employees per department.
The problem arising is that I am able to make cell 'D2' variable, but the rest of the cells remain fixed, whereas I want them to be linked to the firm as selected in 'D2'. Mostly because some of the managers manage multiple firms and will fill in the sheet for multiple firms in one go.
I know that normally a Vlookup is used, but mostly after all information has been consolidated, but now I would like to gather information.

2) In the third sheet 'consolidated' I want to automatically capture the contents of the manager in sheet 2. At this moment I am using the IF function, but this is only one-dimensional. I can for instance capture the info for Firm A in the consolidated sheet if cell 'D2' is set at 'A', but I would also like to see the other firms in this overview. How can I achieve this?

I know problems 2 is actually depending on problem 1, but I hope someone can help me out!

Thanks very much in advance

RvW


I know it might be tough to understand, but