Thread: SUM function (with variable?)

Hi there,
I am at the end of my wits with this problem which seems like it should be an easy one.

I want to populate information from a workbook that's not open using the SUM function and a name in the B1 cell:

Cell B1 = fileName.xlsx

Function = :
=SUM('C:\Folder\[B1]Sheet1'!A10:A100)

I haven't got much "advanced" excel knowledge but coming from a bit of programming experience this looks like it should work. B1 is just a variable and I am plugging in the variable.

My Google-fu has resulted in macros and VB stuff I was really hoping to avoid.

Any thoughts?

Thanks!

Hey thanks for the quick reply daddylonglegs. That is truly a disappointment to hear.

Was M$ not thinking on this one? I've just entered a bit of the business world here and today was really my first excel based side project. Right from the get go I knew that would be a useful thing to have, and no doubt many others have thought so too.

I am grateful for your help though. I guess my question about the Morefunc addon is if I make up a workbook and save it will another person be able to open it and be able to see the same information? I am in a work environment so this may or may not become an issue.

Thank you!

Would there be a way to check if embedding is possible or a guide on how to do so properly?

If not I will messing around with it tomorrow heh.

As mentioned above it will be rather difficult to do using formulas.

Have you considered using a VBA macro to do what you are asking?

The following sounds like it would do what you are after:

Sub Copy()
Dim WorkbookName As String
Dim MainWorkbook As Workbook
Dim Sum as Integer
    
    Set MainWorkbook = ThisWorkbook
    WorkbookName = ActiveSheet.Range("B2")
   
    Workbooks.Open "C:\test\" & WorkbookName
    
    Sum = WorksheetFunction.Sum(Sheets("sheet1").Range("A10:A100"))

    MainWorkbook.Activate
  
    Range("B3").Value = Sum

End Sub

Thanks, I did want to avoid macros (simply I haven't used them before) but I am willing to give it a shot. I will have to look up how to use them and I will report back.

I will also look up Morefunc.

Thanks for the suggestions guys.

Alright so macros out. Morefunc out. Interesting find though.

In B1 is the file name and extension.

If I used =INDIRECT("'C:\MyDocs\Shared\Excel project\["&B1&"]Sheet1'!O13"). I would get a correct value. But I need to sum up a desired range so I tried:

=SUM("'C:\MyDocs\Shared\Excel project\["&B1&"]Sheet1'!O13:O126")

This however does not work. Any thoughts?

By the way with the SUM function I am trying I get a #VALUE error

I thought I tried that and it failed.

Thank you mr. longlegs for your help. My last question for the time being would be is there a way to make the range, of this last function you posted, dynamically grow?

For instance the starting point would always be O13 but the rows might not always go down to O126. Some larger and some bigger. Is there a way to iterate through each row until a NULL is found or something?

Thank you!