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;
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!