Connect data from hundreds of closed workbooks to one workbook

[rgmatos3]

Dear Scientists,

I have a simple problem but I really can't find an elegant solution (not ctrl + c and ctrl v).

Workbook "2014 Sales.xlsx" must have 12 worksheets, one per month, which one must have 31 columns that gets data from workbooks 2014 01 01.xlsx; 2014 01 02.xlsx 2014; 01 03.xlsx;... and so on, as externals links. And I must say that, I can NOT work with 31 different worksheets all open at the same time!

I know that, there is no way to make INDIRECT (....) read from closed workbooks. Period.

Is there a simple way to make that work? Maybe by using =procv(....).... Please don't send me complex solutions (VBA). I don't know how to use that!

I did a small example, but it gives you an idea.

Thanks and keep it easy, literally.

Workbook 2014 Sales
[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"]Sales[/TD]
[TD="width: 64"]Days[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]Shops[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Workbook 2014 01 01
Worksheet NY
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Shops[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]a
[/TD]
[TD="align: right"]101
[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]102[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]103
[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]104[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet CA
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Shops[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]105[/TD]
[/TR]
</tbody>[/TABLE]

Workbook 2014 01 02
Worksheet NY
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Shops[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]201[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]202[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]203[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]204[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet CA
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Shops[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]205[/TD]
[/TR]
</tbody>[/TABLE]

Workbook 2014 01 03
Worksheet NY
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Shops[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]301[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]302[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]303[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]304[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet CA
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Shops[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]305[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jadre]

if u want to connect to other excel documents take get certain information from them go to data tab click existing connections and select a blank one and click what info you want to connect hope it helps