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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2222[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3333[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4444[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

NEW SHEET.xls
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Value of D from Sheet A[/TD]
[TD]Value of D from Sheet B[/TD]
[TD]Value of D from Sheet C[/TD]
[TD]Value of D from Sheet D[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]1 (VLOOKUP)[/TD]
[TD]Not Shown Here[/TD]
[TD]Not Shown Here[/TD]
[TD]Not Shown Here[/TD]
[/TR]
[TR]
[TD]0000[/TD]
[TD]NOT IN LOOKUP[/TD]
[TD]Not Shown Here[/TD]
[TD]Not Shown Here[/TD]
[TD]Not Shown Here[/TD]
[/TR]
[TR]
[TD]4444[/TD]
[TD]3[/TD]
[TD]Not Shown Here[/TD]
[TD]Not Shown Here[/TD]
[TD]Not Shown Here[/TD]
[/TR]
[TR]
[TD]2222[/TD]
[TD]4[/TD]
[TD]Not Shown Here[/TD]
[TD]Not Shown Here[/TD]
[TD]Not Shown Here[/TD]
[/TR]
</tbody>[/TABLE]

 

[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