Search multiple workbooks
 

I have a summary workbook which adds ups cells from a number of different
workbooks. At the moment I am using the formula ='documents and
settings...etc\[file name_a.xls]Sheet1'$J$11+'documents and
settings...etc\[file name_b.xls]Sheet1'$J$11+.....etc.........

Because of the file name sizes I have been forced to only add up 5 or 6 per
cell and have created a number of cells which I then total. The cells doing
the calculations have then been hidden.

This method i am aware increases the likihood of something going wrong so I
am wondering if there is a way I can ask the cell to go into the named folder
and search every workbook within it.

Any help would be greatly appreciated!

Search multiple workbooks
 

http://j-walk.com/ss/excel/tips/tip82.htm

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"upsidedown_pw" wrote in message
...
I have a summary workbook which adds ups cells from a number of different
workbooks. At the moment I am using the formula ='documents and
settings...etc\[file name_a.xls]Sheet1'$J$11+'documents and
settings...etc\[file name_b.xls]Sheet1'$J$11+.....etc.........

Because of the file name sizes I have been forced to only add up 5 or 6
per
cell and have created a number of cells which I then total. The cells
doing
the calculations have then been hidden.

This method i am aware increases the likihood of something going wrong so
I
am wondering if there is a way I can ask the cell to go into the named
folder
and search every workbook within it.

Any help would be greatly appreciated!

Search multiple workbooks
 

Thanks Don.

I have never used VBA before but am determined to work it out.

Having looked at that link it appears that the code is asking to search a
particular work book for something. I want to look at a particular cell and
sheet in all workbooks in a particular folder.

Is there a way to adapt the code to do this?

"Don Guillett" wrote:

http://j-walk.com/ss/excel/tips/tip82.htm

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"upsidedown_pw" wrote in message
...
I have a summary workbook which adds ups cells from a number of different
workbooks. At the moment I am using the formula ='documents and
settings...etc\[file name_a.xls]Sheet1'$J$11+'documents and
settings...etc\[file name_b.xls]Sheet1'$J$11+.....etc.........

Because of the file name sizes I have been forced to only add up 5 or 6
per
cell and have created a number of cells which I then total. The cells
doing
the calculations have then been hidden.

This method i am aware increases the likihood of something going wrong so
I
am wondering if there is a way I can ask the cell to go into the named
folder
and search every workbook within it.

Any help would be greatly appreciated!

Search multiple workbooks
 

There are several ways to do this with a macro.
You can open each file and build a sum
You can make a list and get the value withOUT opening the files
You can make one cell get the values and sum as it goes.
Contact me privately.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"upsidedown_pw" wrote in message
...
Thanks Don.

I have never used VBA before but am determined to work it out.

Having looked at that link it appears that the code is asking to search a
particular work book for something. I want to look at a particular cell
and
sheet in all workbooks in a particular folder.

Is there a way to adapt the code to do this?

"Don Guillett" wrote:

http://j-walk.com/ss/excel/tips/tip82.htm

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"upsidedown_pw" wrote in message
...
I have a summary workbook which adds ups cells from a number of
different
workbooks. At the moment I am using the formula ='documents and
settings...etc\[file name_a.xls]Sheet1'$J$11+'documents and
settings...etc\[file name_b.xls]Sheet1'$J$11+.....etc.........

Because of the file name sizes I have been forced to only add up 5 or 6
per
cell and have created a number of cells which I then total. The cells
doing
the calculations have then been hidden.

This method i am aware increases the likihood of something going wrong
so
I
am wondering if there is a way I can ask the cell to go into the named
folder
and search every workbook within it.

Any help would be greatly appreciated!