Indirect another wookbook

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.