Data extraction and centralization

Hi,

I have 2 workbooks and have to do some data extraction and centralization from one into the other. I need a function that searches for the worksheet names of a workbook(it has a few hundred worksheets) based on the names/numbers sorted into a column in another workbook, and after finding the specified sheet it needs to extract data from specific cells(which are the same within each worksheet of the workbook, i.e. D34,E35,E36,D37). Is there a way to make this possible ? Because it's extremely time consuming to do this manually.

Basically:
workbook1 one has a column with a series of numbers
workbook2 has a series of worksheets corresponding to the numbers in the column of woorkbook1 and data afferent to that specific number, I need to extract that data and append it to the series in workbook1.

Thanks a bunch in advance

Hi

Use of the INDIRECT function could be helpful here.

If, for instance, cell A2 in Sheet1 of Workbook1 contained 12345, and you needed to extract data from cell B2 in Sheet 12345 in Workbook2, you could use something along the lines of:

=INDIRECT("'[Workbook2]"&A2&"'!B2")

However, one thing to note is that the INDIRECT function only works if both workbooks are open. Therefore it's ok for pulling the data out, but once you have it you would need to copy > paste special > values to stop errors appearing as soon as you shut down Workbook2.

Alternatively, there is an INDIRECT.EXT function in an add-in available here:http://download.cnet.com/Morefunc/30...-10423159.html
This will work even when the workbooks are closed.

If neither of these solutions are what you're after, then you're probably looking at going down the macro route.