VLOOKUP in external files

[bmpreston]

So. I have a file (NEW SHEET.XLS) with a column of numbers. Column A. These numbers are referenced (only if they have a corresponding value on one of 11 sheets) as a cell with a value.

These 11 sheets are names A.xls, B.xls, C.xls etc in the same folder on my computer...

I would like to validate (VLOOKUP) that if on sheet a.xls, Column A is the same number on Column A on the NEW SHEET.XLS, we bring a value over from that row.

Sheet A has my static values, I'm looking to formulate my NEW SHEET.XLS as shown below.

Thanks

Please see below Two tables showing two separate xls files:

Sheet A

Column A	Column B	Column C	Column D	Column E
1111			1
2222			4
3333			2
4444			3

<tbody>
</tbody>

NEW SHEET.xls

Column A	Value of D from Sheet A	Value of D from Sheet B	Value of D from Sheet C	Value of D from Sheet D
1111	1 (VLOOKUP)	Not Shown Here	Not Shown Here	Not Shown Here
0000	NOT IN LOOKUP	Not Shown Here	Not Shown Here	Not Shown Here
4444	3	Not Shown Here	Not Shown Here	Not Shown Here
2222	4	Not Shown Here	Not Shown Here	Not Shown Here

<tbody>
</tbody>

 

[FDibbins]

These 11 sheets are names A.xls, B.xls, C.xls etc in the same folder on my computer...

I think you mean files (workbooks), not sheets? (not to be pedantic, but it does make a big difference )

If the referenced file/s will be open, you could use INDIRECT to include the variable file name, but that only works on open workbooks.

1 option might be to install the Morefunc add-in which will allow closed WB's to be used. Find it here...
http://www.ashishmathur.com/tag/morefunc/

Another option (ugly, I know), would be to use 1 series of 11 nested IF statements with your vlookups