Pulling data from multiple excel files

**amjud** · 06-03-2013, 01:05 AM

Hi
I am trying to use the following formula to pull out data from multiple excel files in a folder called “Certificate”.

='D:\SSR Sec and Tech\certificate\[STUDENT 2.xlsx]Student Record'!$B$10

='D:\SSR Sec and Tech\certificate\ Path
[STUDENT 2.xlsx] File Name
Student Record'!$B$10 Sheet name and Cell reference

The formula works without any problem. I want to replicate the formula to extract the same data in multiple excel files. In this case only the second part of the formula needs to change to ‘STUDENT 3’, ‘STUDENT 4’, ‘STUDENT 5’ and so on. I have created a column in excel with those values. I am trying to use the cell contents in the above ‘formula’, but I cannot seem to replace this value in the formula. Needless to mention that I tried to drag the formula, but it does not work. I am attaching the two excel files. If someone knows how to make this work, please help me out.

Thanks in advance

**Pepe Le Mokko** · 06-03-2013, 02:26 AM

Try

Please Login or Register  to view this content.

and pull down as needed

or have a look at this add-in

**amjud** · 06-05-2013, 03:03 AM

Thanks loads mate, but the problem is that this formula works only if the worksheet is open. As soon as I close it, I get a REF error. Is there anything that I can do?

Additionally, I need to have a space between "Student" and "1" i.e. it should be like this in the formula "Student 1". I tried to play with the formula to make it work, but I am not up to that level yet, I guess.

Thanks in advance, if you have anything more to help