how to specify variable range in link?

**4mla1fn** · 07-12-2010, 02:20 PM

i'd like a way to compute based on a variable range to data in a linked file. for example, it's easy to do something static:

=sum([data.xls]data!a1:a15)

however, i need the range portion to be variable, so i could so something like this pseudocode:

=sum([data.xls]data!INDIRECT(c3))

where c3 is computed string like "a1:a15"

the attached sheet has an example.

can this be done just using functions; i.e. no VB code/macros?

thanks.

**protonLeah** · 07-13-2010, 12:31 AM

C3: ="[data.xls]data!A1:A" & A1

**DonkeyOte** · 07-13-2010, 02:42 AM

Note that native INDIRECT will only work if the target file is open.

If you're working with closed files then the native INDIRECT function is not a viable approach I'm afraid and you will be looking at VBA in some guise.
(else modify the links via Edit/Replace as and when required)

**4mla1fn** · 07-13-2010, 08:30 AM

first, *thanks* for the responses.

following protonLeah's lead, i made c3 = "data!A1:A" & C3. where c5 = sum(indirect(c3)), it returned 120, the correct answer. so this works for the references within the workbook.

as a test, i then changed c3 = 'C:\Documents and Settings\user\Desktop\[data.xls]data'!$A$1:$A$20

this returns a #REF! error. i then tried opening data.xls, but that didn't make a difference. (not surprising since INDIRECT is getting its value locally, not from the linked file.)

i'm guessing then that either INDIRECT can't/won't properly return a path, or the recipient function (SUM, in this case) doesn't like what INDIRECT is returning, or maybe there's some combination of quoting of the path that i'm not doing correctly. or what am i missing? am i forced to use VB to do what i need? suggestions/clarifications appreciated.

**4mla1fn** · 07-13-2010, 10:49 AM

okay, the third option was the problem: the quoting i used.

when if had c3 = 'C:\Documents and Settings\rchong\Desktop\[data.xls]data'!$A$1:$A$20

it didn't work. on a whim, i added another single quote (as in two single quotes, not a single double quote) to the front:

c3 = ''C:\Documents and Settings\rchong\Desktop\[data.xls]data'!$A$1:$A$20

this worked! sadly though, but only when data.xls is open. crazy.

so...i guess the problem was with my understanding. it seems INDIRECT returns a reference rather than the contents. (??) i want the contents so that SUM can go open the file and do its thing. hmm...

**4mla1fn** · 07-13-2010, 11:15 AM

i tried named ranges and OFFSET, but they don't work with unopened files either. time to look at VB. any tips of where/how to start? most of my uses would be with array formulas. thanks.