Excel indirect reference to normal reference

[Siva29prakash]

Greetings everyone,

I am creating a employees current position list in Excel.

I have already got their full details in a Excel workbook for each containing personal details, education, experience etc., In a desired format.

Say for example

A Excel file named with employer id number contains 10 worksheet containing the above mentioned details.

In every workbook named with employer id, worksheet named "personal detail" contains "Date of birth" in "$B$6" cell and worksheet named "experience" contains "Date of joining in this company" in "$K$3".

I have been consolidating that "Date of birth" and "Date of joining in this company" of each employee of a company containing 200 employees.

If I use indirect reference, I need to open all the 200 workbooks at a time and then I can get that data.

So, can I have a solution for this.

Note that the employee id is not in a particular order. Since some of the employee may resigned.

Answers are most welcome.

Thanks in advance

[#2]

Are all the 200 files in one folder?

[#3]

@Jan Karel Pieterse 

Thank you for reply.

Yes, all the files are in one folder.

Since I have all my employee ID and as the employee ID will not be continuous. I have listed it in a separate workbook in "Sheet1's" A column and with indirect reference like shown below for "Date of birth"

=indirect("'["&$a2&"]personal_details'!$B$6",1)

Similarly for the "Date of joining"

Is there any other way with direct referencing so that I will not having the problem to open all the 200 employee files at the same time.

While replying this I got a idea that can we concate this to a text and make it workable.

Let me try this and reply you.

Thanks in advance.

With regards,

Sivaprakash