Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search across multiple workbooks | Excel Discussion (Misc queries) | |||
search text in other workbooks | Excel Worksheet Functions | |||
search and link workbooks | Excel Discussion (Misc queries) | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
search through workbooks | Excel Worksheet Functions |