Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing totals from varied number of workbooks
I am trying to consolidate totals into one workbook from a varied number of
other workbooks that are saved in the same folder. All workbooks are identical, just filled out by different users weekly. workbooks are named user1.xls user2.xls user3.xls etc... all workbook names start with USER and are followed by a number .xls There is a sheet in each workbook called TOTALS which calculates from other sheets in the workbook The TOTALS sheet in each book has a range of information from B2 to P27. The Master.xls has an Identical TOTALS sheet and I would like cell B2 in the master to be the sum of cell B2 from the TOTALS sheet from all USER workbooks in the folder. And Cell B3 to be the the sum of Cell B3 From the TOTALS sheet from all USER workbooks in the folder. The number of USER folders must be flexible as each week there may be a different number of USER files in the folder. all files, including the master file would be saved in c:\tracking folder. I am sure I have to do some For Statements and probably some while statements, but have never worked with a non set number of files before. Any help would be greatly appreciated. Wally Steadman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing totals from varied number of workbooks
In Master.xls TOTALS cell B2 type:
='[USER1.xls]TOTALS'!$B$2 + '[USER2.xls]TOTALS'!$B$2 + '[USER3.xls]TOTALS'!$B$2 ' and on and on. Just include all worksheets and it will not matter if some workbooks have a blank cell B2. When you open the Master file, you will be asked if you wish to update links. Clicking "Yes" will enable the workbook to show up to date totals in all the related workbooks. I hope this is what you are looking for. -- Best wishes, Jim "Wally Steadman" wrote: I am trying to consolidate totals into one workbook from a varied number of other workbooks that are saved in the same folder. All workbooks are identical, just filled out by different users weekly. workbooks are named user1.xls user2.xls user3.xls etc... all workbook names start with USER and are followed by a number .xls There is a sheet in each workbook called TOTALS which calculates from other sheets in the workbook The TOTALS sheet in each book has a range of information from B2 to P27. The Master.xls has an Identical TOTALS sheet and I would like cell B2 in the master to be the sum of cell B2 from the TOTALS sheet from all USER workbooks in the folder. And Cell B3 to be the the sum of Cell B3 From the TOTALS sheet from all USER workbooks in the folder. The number of USER folders must be flexible as each week there may be a different number of USER files in the folder. all files, including the master file would be saved in c:\tracking folder. I am sure I have to do some For Statements and probably some while statements, but have never worked with a non set number of files before. Any help would be greatly appreciated. Wally Steadman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing totals from varied number of workbooks
Jim,
Thanks for your reply. That does work and I am currently doing that now, but where i run in to issues is if I currently have 20 sheets to sum and then have to add another 10 sheets, I have to go in and edit each formula and there are currently 390 cells that contain the formula. Gets to be a bigger job than it needs to be. I am sure there is a way in VBA code to have it look at all user.xls files in a folder and sum specific cells but that is where I am lost, dusting off some of my VBA books now and using the forums I know the answer is there. Just hoping it comes to me sooner rather than later as the organization I am helping out (my wife's job :) ) would like to implement this as soon as possible. Thanks again for the help Wally Steadman "Jim Jackson" wrote in message ... In Master.xls TOTALS cell B2 type: ='[USER1.xls]TOTALS'!$B$2 + '[USER2.xls]TOTALS'!$B$2 + '[USER3.xls]TOTALS'!$B$2 ' and on and on. Just include all worksheets and it will not matter if some workbooks have a blank cell B2. When you open the Master file, you will be asked if you wish to update links. Clicking "Yes" will enable the workbook to show up to date totals in all the related workbooks. I hope this is what you are looking for. -- Best wishes, Jim "Wally Steadman" wrote: I am trying to consolidate totals into one workbook from a varied number of other workbooks that are saved in the same folder. All workbooks are identical, just filled out by different users weekly. workbooks are named user1.xls user2.xls user3.xls etc... all workbook names start with USER and are followed by a number .xls There is a sheet in each workbook called TOTALS which calculates from other sheets in the workbook The TOTALS sheet in each book has a range of information from B2 to P27. The Master.xls has an Identical TOTALS sheet and I would like cell B2 in the master to be the sum of cell B2 from the TOTALS sheet from all USER workbooks in the folder. And Cell B3 to be the the sum of Cell B3 From the TOTALS sheet from all USER workbooks in the folder. The number of USER folders must be flexible as each week there may be a different number of USER files in the folder. all files, including the master file would be saved in c:\tracking folder. I am sure I have to do some For Statements and probably some while statements, but have never worked with a non set number of files before. Any help would be greatly appreciated. Wally Steadman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing totals from varied number of workbooks
Jim,
I tried doing this again with a file missing and it does not work properly. Asks if I want to link files and I say yes then it says that some of the linked files could not be found and I continue but it does not update properly. Problem solution is much more complicated that I imagined. "Jim Jackson" wrote in message ... In Master.xls TOTALS cell B2 type: ='[USER1.xls]TOTALS'!$B$2 + '[USER2.xls]TOTALS'!$B$2 + '[USER3.xls]TOTALS'!$B$2 ' and on and on. Just include all worksheets and it will not matter if some workbooks have a blank cell B2. When you open the Master file, you will be asked if you wish to update links. Clicking "Yes" will enable the workbook to show up to date totals in all the related workbooks. I hope this is what you are looking for. -- Best wishes, Jim "Wally Steadman" wrote: I am trying to consolidate totals into one workbook from a varied number of other workbooks that are saved in the same folder. All workbooks are identical, just filled out by different users weekly. workbooks are named user1.xls user2.xls user3.xls etc... all workbook names start with USER and are followed by a number .xls There is a sheet in each workbook called TOTALS which calculates from other sheets in the workbook The TOTALS sheet in each book has a range of information from B2 to P27. The Master.xls has an Identical TOTALS sheet and I would like cell B2 in the master to be the sum of cell B2 from the TOTALS sheet from all USER workbooks in the folder. And Cell B3 to be the the sum of Cell B3 From the TOTALS sheet from all USER workbooks in the folder. The number of USER folders must be flexible as each week there may be a different number of USER files in the folder. all files, including the master file would be saved in c:\tracking folder. I am sure I have to do some For Statements and probably some while statements, but have never worked with a non set number of files before. Any help would be greatly appreciated. Wally Steadman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing totals from varied number of workbooks -- code: DocumentWorkbookLinks
Hi Wally,
open your workbook without updating the links (or formulas with linked values will turn into an error) then, put this sub in a general module and run it. A new workbook will be created with a sheet (WorkbookLinks) that lists all the cells that have formulas with links and what the formula is. You could, of course, modify this code to simply list the workbooks that are referenced. If any of the files are no longer available, you can go to the menu and choose: Edit, Links... to remap what they are linked to '~~~~~~~~~~~~~~~~~ Sub DocumentWorkbookLinks() 'strive4peace2007 at yahoo.com Dim msourceWB As String, mTargetWB As String Dim mLink As String Dim c As Range, s As Worksheet, sS As Worksheet Dim mLastRow As Long, mLastcol As Long msourceWB = ActiveWorkbook.Name Workbooks.Add mTargetWB = ActiveWorkbook.Name ActiveSheet.Name = "WorkbookLinks" Cells(1, 5).Value = msourceWB Cells(1, 1).Value = "Sheet" Cells(1, 2).Value = "Cell" Cells(1, 3).Value = "Formula" Cells(1, 4).Value = "Contents" Dim i As Integer, mRow As Long, j As Long Set s = Workbooks(mTargetWB).Sheets("WorkbookLinks") mRow = 1 On Error Resume Next alinks = Workbooks(msourceWB).LinkSources Application.DisplayStatusBar = True If IsEmpty(alinks) Then MsgBox "No Links were found." Exit Sub End If For j = 1 To UBound(alinks) mLink = "" For i = Len(alinks(j)) To 2 Step -1 If Mid(alinks(j), i, 1) = "\" Then ' mLink = Trim(Left(alinks(j), i) _ & "[" & Mid(alinks(j), i + 1, 255)) mLink = Trim(Mid(alinks(j), i + 1, 255)) i = 2 End If Next i For i = 1 To Workbooks(msourceWB).Sheets.Count Set sS = Workbooks(msourceWB).Sheets(i) DoEvents Application.StatusBar = alinks(j) _ & " ... " & sT.Name Set c = sS.Cells.Find( _ What:=mLink, LookIn:=xlFormulas, _ LookAt:=xlPart) Do While Not c Is Nothing mRow = mRow + 1 s.Cells(mRow, 3) = """" & c.Formula & """" s.Cells(mRow, 1) = sS.Name s.Cells(mRow, 2) = c.Address(False, False) s.Cells(mRow, 4) = c.Value mLastRow = c.Row mLastcol = c.Column Set c = sS.Cells.FindNext(After:=c) If c.Row < mLastRow Then Set c = Nothing Else If (c.Row = mLastRow) And _ (c.Column = mLastcol) Then Set c = Nothing End If End If Loop Next i Next j documentWBlinks_exit: Set c = Nothing Set s = Nothing Set sS = Nothing Application.StatusBar = "" End Sub '~~~~~~~~~~~~~~~~~~~~~ Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * Wally Steadman wrote: Jim, I tried doing this again with a file missing and it does not work properly. Asks if I want to link files and I say yes then it says that some of the linked files could not be found and I continue but it does not update properly. Problem solution is much more complicated that I imagined. "Jim Jackson" wrote in message ... In Master.xls TOTALS cell B2 type: ='[USER1.xls]TOTALS'!$B$2 + '[USER2.xls]TOTALS'!$B$2 + '[USER3.xls]TOTALS'!$B$2 ' and on and on. Just include all worksheets and it will not matter if some workbooks have a blank cell B2. When you open the Master file, you will be asked if you wish to update links. Clicking "Yes" will enable the workbook to show up to date totals in all the related workbooks. I hope this is what you are looking for. -- Best wishes, Jim "Wally Steadman" wrote: I am trying to consolidate totals into one workbook from a varied number of other workbooks that are saved in the same folder. All workbooks are identical, just filled out by different users weekly. workbooks are named user1.xls user2.xls user3.xls etc... all workbook names start with USER and are followed by a number .xls There is a sheet in each workbook called TOTALS which calculates from other sheets in the workbook The TOTALS sheet in each book has a range of information from B2 to P27. The Master.xls has an Identical TOTALS sheet and I would like cell B2 in the master to be the sum of cell B2 from the TOTALS sheet from all USER workbooks in the folder. And Cell B3 to be the the sum of Cell B3 From the TOTALS sheet from all USER workbooks in the folder. The number of USER folders must be flexible as each week there may be a different number of USER files in the folder. all files, including the master file would be saved in c:\tracking folder. I am sure I have to do some For Statements and probably some while statements, but have never worked with a non set number of files before. Any help would be greatly appreciated. Wally Steadman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Varied accuracy custom number formats | Excel Discussion (Misc queries) | |||
Looking for certain months and summing the totals | Excel Worksheet Functions | |||
Combine varied data from separate workbooks into one chart | Charts and Charting in Excel | |||
Summing Weekly Totals into Monthly Totals | Excel Worksheet Functions | |||
Help with Summing Up Totals | Excel Discussion (Misc queries) |