Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Add a cell from multiple spreadsheets

Posted on 2012-09-17
3
Medium Priority
?
217 Views
Last Modified: 2012-09-19
Hi,

I have many spreadsheets all in the same folder on the network. Within each of the spreadsheet, there is a value in the same cell (B16), I would like a spreadsheet to be able add all the values from this cell from all the spreadsheets together and display the total.

How can this be done?
0
Comment
Question by:daiwhyte
3 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 38405594
Try:

e.g.

=SUM('Start:End'!B16)

where Start is first sheetname and End is last sheetname
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 38405955
Hi

I guess the OP is talking about workbooks. If so, try

Sub kTest()
    
    Dim i           As Long
    Dim fn          As String
    Dim MyFolder    As String
    Dim MySum       As Double
    Dim MyVal       As String
    
    MyFolder = "C:\Temp"  'adjust to suit
    
    If Right(MyFolder, 1) <> "\" Then MyFolder = MyFolder & "\"
    
    fn = Dir(MyFolder & "*.xls*")
    
    Do While fn <> vbNullString
        If fn <> ThisWorkbook.Name Then
            MyVal = "'" & MyFolder & "[" & fn & "]Sheet1'!R16C2"
            MySum = MySum + ExecuteExcel4Macro(MyVal)
        End If
        fn = Dir()
    Loop
    
    MsgBox MySum
    'Range("B2") = MySum
    
End Sub

Open in new window


Kris
0
 

Author Closing Comment

by:daiwhyte
ID: 38412646
thank you
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question