summing totals from varied number of workbooks

[Wally Steadman]

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

 

[Jim Jackson]

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.

 

[Wally Steadman]

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

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

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

 

[Wally Steadman]

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.

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

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

 

[strive4peace]

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

*

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.

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


:

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