General discussion

Linking Workbooks in Formula- Excel 2003

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
Follow
Reply to: Linking Workbooks in Formula- Excel 2003
PLEASE NOTE: Do not post advertisements, offensive materials, profanity, or personal attacks. Please remember to be considerate of other members. If you are new to the CNET Forums, please read our CNET Forums FAQ. All submitted content is subject to our Terms of Use.
Reporting: Linking Workbooks in Formula- Excel 2003
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Comments
- Collapse -
Interesting.

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

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

CNET Forums