INDIRECT("[Book2.xls]Sheets!A1") ::: Here you have not mentioned the sheet
name.

Please try the formula as such =INDIRECT("[" & A2 & "]" & A3 & "!" & "A1")
with
A1 = Hello
A2 = Book2
A3 = Sheet1


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


"BimboUK" wrote:

I opened a new workbook and input as you said but I am getting a #REF error.
When evaluating the formula it gets to INDIRECT("[Book2.xls]Sheets!A1") then
next step is the #REF error (I did it in Book2 so input Book2 instead of
Book1 in A2).

Any ideas whats going wrong?



"Jacob Skaria" wrote:

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

Sorry the sheet name was correct ie Sheet1 but I omitted it on my reply - any
ideas


"Jacob Skaria" wrote:

INDIRECT("[Book2.xls]Sheets!A1") ::: Here you have not mentioned the sheet
name.

Please try the formula as such =INDIRECT("[" & A2 & "]" & A3 & "!" & "A1")
with
A1 = Hello
A2 = Book2
A3 = Sheet1


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


"BimboUK" wrote:

I opened a new workbook and input as you said but I am getting a #REF error.
When evaluating the formula it gets to INDIRECT("[Book2.xls]Sheets!A1") then
next step is the #REF error (I did it in Book2 so input Book2 instead of
Book1 in A2).

Any ideas whats going wrong?



"Jacob Skaria" wrote:

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