To understand INDIRECT try this

1. Open new workbook.
2. Enter the text 'Hello' in A1
2. Enter Workbook name in A2 eg: Book1.xls
3. Enter Sheet name in A3 eg: Sheet1
4. In A4 enter the formula which should return the value of current sheet A1
(Hello)

=INDIRECT("[" & A2 & "]" & A3 & "!" & "A1")

If this post helps click Yes
---------------
Jacob Skaria


"BimboUK" wrote:

I wish to use one or two cells to vary part of a worksheet location in a
SUMIF formula.

example =SUMIF('c:\My Documents\[$A$1 C$2.xls]Costing
summary'!$A$28:$A$847,$A5,'c:\My Documents\[$A$1 C$2.xls]Costing
summary'!$C$28:$C$847)

Basically I am wanting to analyse the same range from a costing summary for
each person but also future proof it as A1 = the year and C2 being the name
of the person. The worksheet name is "09-10 A Person".

I have tried INDIRECT but don't understand the SYNTAX correctly yet.

At present I have the info in TEXT format.

If i can get this right I can really use the data I have!!

PS I have tried MoreFunc by ? Laurent using the INDIRECT.ext func and
couldn't get that to work either.

All help greatly appreciated