I need to use an Indirect to another wookboot that has a multi-word name ie
'expense report' not something like 'er' in my excel I did the following to
test
a1 = [expense report]
b1 = sheet1
c1 = a2
d1 = indirect(a1&b1&"!"&c1)

if I use a name in a1 with no spaces ie 'er' then the indirect works but if
I use the above example I always get #ref error in cell. Anyone got any
ideas?

I also did try d1 = indirect("'"&a1&b1&"'!"&c1). It didn't work either.

Remove the brackets from cell A1 and add ".xls" to your
string:

=INDIRECT("'["&A1&".xls]"&B1&"'!"&C1)

HTH
Jason
Atlanta, GA

-----Original Message-----
I need to use an Indirect to another wookboot that has a
multi-word name ie
'expense report' not something like 'er' in my excel I
did the following to
test
a1 = [expense report]
b1 = sheet1
c1 = a2
d1 = indirect(a1&b1&"!"&c1)

if I use a name in a1 with no spaces ie 'er' then the
indirect works but if
I use the above example I always get #ref error in
cell. Anyone got any
ideas?

I also did try d1 = indirect("'"&a1&b1&"'!"&c1). It
didn't work either.


.

Sorry that didn't work. Anything else?

"Jason Morin" wrote:

Remove the brackets from cell A1 and add ".xls" to your
string:

=INDIRECT("'["&A1&".xls]"&B1&"'!"&C1)

HTH
Jason
Atlanta, GA

-----Original Message-----
I need to use an Indirect to another wookboot that has a
multi-word name ie
'expense report' not something like 'er' in my excel I
did the following to
test
a1 = [expense report]
b1 = sheet1
c1 = a2
d1 = indirect(a1&b1&"!"&c1)

if I use a name in a1 with no spaces ie 'er' then the
indirect works but if
I use the above example I always get #ref error in
cell. Anyone got any
ideas?

I also did try d1 = indirect("'"&a1&b1&"'!"&c1). It
didn't work either.


.

Tim,

Indirect can only be used with an open workbook. The last formula works fine
for me with an open workbook else you get #REF.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tim_o_mast" wrote in message
...
I need to use an Indirect to another wookboot that has a multi-word name
ie
'expense report' not something like 'er' in my excel I did the following
to
test
a1 = [expense report]
b1 = sheet1
c1 = a2
d1 = indirect(a1&b1&"!"&c1)

if I use a name in a1 with no spaces ie 'er' then the indirect works but
if
I use the above example I always get #ref error in cell. Anyone got any
ideas?

I also did try d1 = indirect("'"&a1&b1&"'!"&c1). It didn't work either.

If you have a file called Junk Junk.XLS and in another file you type = and
then click on a cell in the first file you get a formula such as ='[Junk
junk.xls]Sheet1'!$C$1
So with in Book1 when I use :
A1: [junk junk]
B1: Sheet1
C1: A2
D1: =INDIRECT("'"&A1&B1&"'!"&C1)
I am able to get the value from A2 in the Junk Junk file
To make it clearer I will replace single quotes (apostrophes) by asterisks
=INDIRECT("*"&A1&B1&"*!"&C1)


best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tim_o_mast" wrote in message
...
I need to use an Indirect to another wookboot that has a multi-word name ie
'expense report' not something like 'er' in my excel I did the following
to
test
a1 = [expense report]
b1 = sheet1
c1 = a2
d1 = indirect(a1&b1&"!"&c1)

if I use a name in a1 with no spaces ie 'er' then the indirect works but
if
I use the above example I always get #ref error in cell. Anyone got any
ideas?

I also did try d1 = indirect("'"&a1&b1&"'!"&c1). It didn't work either.

I do have the other workbook open and it didn't work for me. Anyone else?

"Bernard Liengme" wrote:

If you have a file called Junk Junk.XLS and in another file you type = and
then click on a cell in the first file you get a formula such as ='[Junk
junk.xls]Sheet1'!$C$1
So with in Book1 when I use :
A1: [junk junk]
B1: Sheet1
C1: A2
D1: =INDIRECT("'"&A1&B1&"'!"&C1)
I am able to get the value from A2 in the Junk Junk file
To make it clearer I will replace single quotes (apostrophes) by asterisks
=INDIRECT("*"&A1&B1&"*!"&C1)


best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tim_o_mast" wrote in message
...
I need to use an Indirect to another wookboot that has a multi-word name ie
'expense report' not something like 'er' in my excel I did the following
to
test
a1 = [expense report]
b1 = sheet1
c1 = a2
d1 = indirect(a1&b1&"!"&c1)

if I use a name in a1 with no spaces ie 'er' then the indirect works but
if
I use the above example I always get #ref error in cell. Anyone got any
ideas?

I also did try d1 = indirect("'"&a1&b1&"'!"&c1). It didn't work either.

It works for me. Do you want to email (my private address) a sample file for
me to look at?

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tim_o_mast" wrote in message
...
I do have the other workbook open and it didn't work for me. Anyone else?

"Bernard Liengme" wrote:

If you have a file called Junk Junk.XLS and in another file you type =
and
then click on a cell in the first file you get a formula such as ='[Junk
junk.xls]Sheet1'!$C$1
So with in Book1 when I use :
A1: [junk junk]
B1: Sheet1
C1: A2
D1: =INDIRECT("'"&A1&B1&"'!"&C1)
I am able to get the value from A2 in the Junk Junk file
To make it clearer I will replace single quotes (apostrophes) by
asterisks
=INDIRECT("*"&A1&B1&"*!"&C1)


best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tim_o_mast" wrote in message
...
I need to use an Indirect to another wookboot that has a multi-word name
ie
'expense report' not something like 'er' in my excel I did the
following
to
test
a1 = [expense report]
b1 = sheet1
c1 = a2
d1 = indirect(a1&b1&"!"&c1)

if I use a name in a1 with no spaces ie 'er' then the indirect works
but
if
I use the above example I always get #ref error in cell. Anyone got
any
ideas?

I also did try d1 = indirect("'"&a1&b1&"'!"&c1). It didn't work
either.

Forgot to add: if file Junk Junk is not open you get REF error

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tim_o_mast" wrote in message
...
I need to use an Indirect to another wookboot that has a multi-word name ie
'expense report' not something like 'er' in my excel I did the following
to
test
a1 = [expense report]
b1 = sheet1
c1 = a2
d1 = indirect(a1&b1&"!"&c1)

if I use a name in a1 with no spaces ie 'er' then the indirect works but
if
I use the above example I always get #ref error in cell. Anyone got any
ideas?

I also did try d1 = indirect("'"&a1&b1&"'!"&c1). It didn't work either.

tim_o_mast wrote...
I need to use an Indirect to another wookboot that has a multi-word
name ie
'expense report' not something like 'er' in my excel I did the
following to
test
a1 = [expense report]
b1 = sheet1
c1 = a2
d1 = indirect(a1&b1&"!"&c1)

if I use a name in a1 with no spaces ie 'er' then the indirect works
but if
I use the above example I always get #ref error in cell. Anyone got
any
ideas?

I also did try d1 = indirect("'"&a1&b1&"'!"&c1). It didn't work
either.

If the other workbook is named "expense report.xls" (without the double
quotes, which I included only to delimit the filename including the
space char), and if that workbook is open in the *same* Excel session
as the workbook containing the formula, then you'd get a #REF! error
because your A1 cell didn't include the ".xls" at the end of the
filename. Try changing A1 to

[expense report.xls]

If you still get #REF! errors, then in a blank cell type = and then
press the [Ctrl]+[F6] key combination repeatedly until Excel activates
the expense report workbook, then move the active cell to Sheet1!A2 in
that workbook and press [Enter]. What's the resulting formula in the
original workbook?

If you mean that the workbook's filename on disk includes the square
brackets, then your problem is partially self-inflicted. The exercise
in the preceding paragraph is meant to determine whether this is the
case.