Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Linking Workbooks in Formula- Excel 2003

Apr 2, 2010 5:21AM PDT

I am trying to link workbooks together and am stuck.

In Book1.xls I have 1 column which contains the name of my other workbooks (lets say Book2.xls, Book3.xls, etc...). I am attempting to create a formula which looks up the maximum(max) number in a given range in a different workbook (Book2.xls). I am able to do this by this: =MAX([Book2.xls]Sheet1!A1:[Book2.xls]Sheet1!A10)
What I want to do is rather than having to type in the [Book2.xls] I would like to reference a cell already on my current sheet that says Book2.xls. My theory is something along the lines of: =MAX([A1]Sheet1!A1:[A1]Sheet1!A10) but I am unable to figure anything out which actually allows me to do this.

Thanks, any help would be greatly appreciated!

Discussion is locked

- Collapse -
Interesting.
Apr 2, 2010 6:27AM PDT

I don't have my Excel available at the moment, (I'm using a different machine for this), so I can't test it for you.

I can see what you want to do and my feeling is that it should be possible, but I can't see how myself at the moment.

If your other workbook is named book2.xls, then enter that text in cell A1 and use that content to point to cells in book2.xls.

Have you tried =MAX ( [ (A1) ] Sheet1!A1 : [ (A1) ] Sheet1!A10 )

I put the spaces in to show that A1 is enclosed in () brackets.

How about another way. I've never used it myself for this but I know it is possible. What about "Named Ranges"? If you name a range in Book2.xls, then instead of using " =MAX([Book2.xls]Sheet1!A1:[Book2.xls]Sheet1!A10) ", you just use the range name; eg

If cells A1 to A30 in book2.xls is given the name range of Data, then in your main workbook, define Data as a range as =Book2.xls!Data

That way, you use =MAX(Data) instead of the long winded naming.

Would that help?

More about name ranges here;
http://www.exceluser.com/explore/rangeroundup.htm

Mark

- Collapse -
Re: addresses
Apr 2, 2010 11:59PM PDT

Back in 1955, when the only available programming language was Assembler, such was called an 'indirect address'. You put the address of a variable in register R1, and said something like add_indirect R1,1.
That would add 1 not to the value contained in R1, but to the variable of which R1 contained the address.

The designers of Excel made the function "indirect" for such. http://www.cpearson.com/excel/indirect.htm tells you more. This was the first google hit for EXCEL INDIRECT ADDRESSING. I'm sure you can find other examples.

Best of luck in your experiments.

Jees