Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating sums in different spreadsheets (2003)

    Hi all,
    I have a group of about 50 spreadsheets in the one directory. Each spreadsheet contains three workbooks titled

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating sums in different spreadsheets (2003)

    I think you are using the sheet/book the wrong way. The workbook is a file that can contain many worksheets.

    You can create a list of the 50 files sums for all the sheets like (50 separate calculations, each changing the filename]
    =SUM(G:Comm ServicesMMRProject Management24 AccessoriesHolstersTrial

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating sums in different spreadsheets (2003)

    Further to Steve's response, If you will not always have the same files in the directory of interest, the following code can be placed in a module of the master workbook and used to capture the data.

    If the data you are summing is not always whole numbers you will need to dimension TAS, TBS and TCS to Single or Double depending on the level of accuracy you require.

    You will also need to revise the sheet names and target cells that apply in the master workbook.
    <pre>Option Explicit
    Sub SumCell()
    Dim TAS As Long ' Trial A Sum
    Dim TBS As Long ' Trial B Sum
    Dim TCS As Long ' Trial C Sum
    Dim wbk As Workbook
    Dim FSpec As String
    Dim PathSpec As String
    Dim FoundFile As String

    PathSpec = "G:Comm ServicesMMRProject Management" _
    & "24 AccessoriesHolstersTrial
    Regards
    Don

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating sums in different spreadsheets (2003)

    Don/Steve,
    Thanks so much for your advice. I will give it a go and report back how it turned out. Thanks again.

    Bill.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •