 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2007Add cells across multiple workbooks that increase...
I will try to explain this the best that I can... I have around 100 excel workbooks that contain the same exact sheets in each. In each of these worksheets there are cells that contain a number value that I would like to total on one 'master' sheet. These workbooks are in there own folders but the workbooks all have the same name. As time goes on there will be more folders and workbooks created that I want to total without having to change the master. Is there a way to count the value of a cell from these workbooks and not have to be concerned if there are additional workbooks added? I was thinking of some type of VBA or script that could run and go out and search these folders for that file name then count the cells... Is there a better way to do this? Thanks in advance. Hi Steve Are there multiple sheets in each book with values, or just a single sheet? If there is a single sheet, is there any reason not to have them all in the same workbook, as that would make the task much easier?  SignatureRegards Roger Govier >I will try to explain this the best that I can... > [quoted text clipped - 19 lines] > > Is there a better way to do this? Thanks in advance. There are several sheets in the workbook but only the values of the one sheet (which also have the same name) are needed. The reason that they are seperate is that these workbooks are being used as estimates for the company I work for. Each estimate must be in a unique folder. What I am trying to accomplish is gathering how many devices of each type I need from these estimates. > Hi Steve > [quoted text clipped - 26 lines] > > > > Is there a better way to do this? Thanks in advance. > There are several sheets in the workbook but only the values of the one > sheet [quoted text clipped - 5 lines] > accomplish is gathering how many devices of each type I need from these > estimates. A couple of more facts about the layout might be useful. Are all the folders (directories to old timers like me<g>) containing estimates under the same master folder? That is, if we go to the master folder, can we restrict are search to just folders located within it? If so, are there any other folders inside the master folder that do not contain estimates? If so, are the folders with estimates named in some similar way so that they can easily be distinguished from the non-estimate folders? What I am trying to get at is a method to identify where the workbooks are without having to search the entire hard disk(s) looking for them. In the end, I am not sure I will be able to answer your question, but those with the knowledge to do so should find this information useful. Rick Hi Steve That being the case, Ron de Bruin has a lot of code available on his site for performing this type of task. I think the most appropriate would be http://www.rondebruin.nl/copy3.htm which you could modify to suit your needs. SignatureRegards Roger Govier > There are several sheets in the workbook but only the values of the > one sheet [quoted text clipped - 41 lines] >> > >> > Is there a better way to do this? Thanks in advance. The rdbmerge addin that Ron has is getting closer, however it does not scan all of the subfolders (directories), even when selected to do so... The file structure is as such (with different names); Home - Building 1 - 102 Charly - Documents - 104 Dave - Documents - 111 Fred - Documents - Building 2 - 221 Mary - Documents - 234 Sue - Documents - 258 Lisa - Documents The xls file is in the documents folder. The workbook has the same name and the worksheet is also the same name. It is on a network mapped drive. I am going to play around with some of the other utilities available on that site but still have not a foolproof way of getting this to work, yet. > Hi Steve > [quoted text clipped - 50 lines] > >> > > >> > Is there a better way to do this? Thanks in advance. > The file structure is as such (with different names); > [quoted text clipped - 17 lines] > and > the worksheet is also the same name. It is on a network mapped drive. A follow up question... are you looking to combine worksheets in all the Building directories taken together, or in each Building directory individually? Rick I am looking to total the amount of a certain cell taken from the first worksheet from each workbook in all directories and subdirectories. The file and worksheet name is the same in each directory and subdirectory. This total will then be displayed in a new xls file which will keep an updated quanity. I have been playing with more scripting, only I can not find one that will successfully make it down through all of the subdirectories... > > The file structure is as such (with different names); > > [quoted text clipped - 23 lines] > > Rick My code only use the files in the folders below the root dir You can see the code on my FSO page and change it so it will loop also through the files in the folders below the sub folders http://www.rondebruin.nl/fso.htm SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > The rdbmerge addin that Ron has is getting closer, however it does not scan > all of the subfolders (directories), even when selected to do so... [quoted text clipped - 76 lines] >> >> > >> >> > Is there a better way to do this? Thanks in advance. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.