SUM function (with variable?)

[motos]

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!

[daddylonglegs]

Usually to use a filename from a cell you need to use INDIRECT function.......but INDIRECT doesn't work with closed workbooks......

One possible workaround is to download Morefunc add-in (should be able to google it) and use INDIRECT.EXT function which does work with closed workbooks. Then you'd use a formula like

=SUM(INDIRECT.EXT("'C:\Folder\["&B1&"]Sheet1'!A10:A100"))

[motos]

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!

[Paul]

If you use a function from an 3rd party add-in like MOREFUNC, any user that views your spreadsheet would also need that add-in installed. (Unless you used something like Terminal Services or a Published Desktop/Citrix to access Excel. It could then be installed on the server and each client would see the add-in.)

[daddylonglegs]

You used to be able to "embed" Morefunc in a workbook so that it would be available to those who you sent it to (it's then effectively VBA code within that workbook) but I believe that option wasn't available in Excel 2007 last time I checked.........

[motos]

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.

[fullysic]

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:

Please Login or Register  to view this content.

[motos]

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.

[motos]

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?

[motos]

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

[daddylonglegs]

You still need INDIRECT even with the SUM function (like my earlier example with INDIRECT.EXT)

INDIRECT supplies SUM with a valid cell reference....., i.e.

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

[motos]

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!

[Paul]

You can create a dynamic named range, which should work for you here.

http://www.contextures.com/xlnames01.html#Dynamic

Let us know if you can't get this to work.