Indirect

i am referencing a worksheet using the indirect function.
excel is automatically adding the active worksheet name to the formula which
returns #REF. how can i how can i stop it from automatically adding the sheet
name?

thanks,

Assume we can't see your worksheet and formula you are attempting to create
using the INDIRECT function.

Now try a re-post with that information.


Gord Dibben MS Excel MVP

On Tue, 26 Aug 2008 05:57:01 -0700, waldorf
wrote:

i am referencing a worksheet using the indirect function.
excel is automatically adding the active worksheet name to the formula which
returns #REF. how can i how can i stop it from automatically adding the sheet
name?

thanks,

i have used this formula before (with 2003) without problems.
=INDIRECT("'" & B13)&"!"&"H56"
i am using excel 2007 now.
when i step through the "Evaluate Formula", the result on the side shows the
cell reference with the workseet name.

"Summary!$B$13" (B13 contains the sheet name i am referencing)
the result is "Summary!Option1!H56 which is not valid.
i need it to drop the current worksheet (Summary).
i can't find a setting or option to tell it not to do this.

i hope this makes more sense.


"Gord Dibben" wrote:

Assume we can't see your worksheet and formula you are attempting to create
using the INDIRECT function.

Now try a re-post with that information.


Gord Dibben MS Excel MVP

On Tue, 26 Aug 2008 05:57:01 -0700, waldorf
wrote:

i am referencing a worksheet using the indirect function.
excel is automatically adding the active worksheet name to the formula which
returns #REF. how can i how can i stop it from automatically adding the sheet
name?

thanks,

=INDIRECT("'" & B13)&"!"&"H56"
B13 contains the sheet name i am referencing

Try it like this:

=INDIRECT("'"&B13&"'!H56")

Resolves to: =Summary!H56

--
Biff
Microsoft Excel MVP


"waldorf" wrote in message
...
i have used this formula before (with 2003) without problems.
=INDIRECT("'" & B13)&"!"&"H56"
i am using excel 2007 now.
when i step through the "Evaluate Formula", the result on the side shows
the
cell reference with the workseet name.

"Summary!$B$13" (B13 contains the sheet name i am referencing)
the result is "Summary!Option1!H56 which is not valid.
i need it to drop the current worksheet (Summary).
i can't find a setting or option to tell it not to do this.

i hope this makes more sense.


"Gord Dibben" wrote:

Assume we can't see your worksheet and formula you are attempting to
create
using the INDIRECT function.

Now try a re-post with that information.


Gord Dibben MS Excel MVP

On Tue, 26 Aug 2008 05:57:01 -0700, waldorf
wrote:

i am referencing a worksheet using the indirect function.
excel is automatically adding the active worksheet name to the formula
which
returns #REF. how can i how can i stop it from automatically adding the
sheet
name?

thanks,

it actually resolves to "Option1!H56"
which is what i needed.

thank you very much!

"T. Valko" wrote:

=INDIRECT("'" & B13)&"!"&"H56"
B13 contains the sheet name i am referencing

Try it like this:

=INDIRECT("'"&B13&"'!H56")

Resolves to: =Summary!H56

--
Biff
Microsoft Excel MVP


"waldorf" wrote in message
...
i have used this formula before (with 2003) without problems.
=INDIRECT("'" & B13)&"!"&"H56"
i am using excel 2007 now.
when i step through the "Evaluate Formula", the result on the side shows
the
cell reference with the workseet name.

"Summary!$B$13" (B13 contains the sheet name i am referencing)
the result is "Summary!Option1!H56 which is not valid.
i need it to drop the current worksheet (Summary).
i can't find a setting or option to tell it not to do this.

i hope this makes more sense.


"Gord Dibben" wrote:

Assume we can't see your worksheet and formula you are attempting to
create
using the INDIRECT function.

Now try a re-post with that information.


Gord Dibben MS Excel MVP

On Tue, 26 Aug 2008 05:57:01 -0700, waldorf
wrote:

i am referencing a worksheet using the indirect function.
excel is automatically adding the active worksheet name to the formula
which
returns #REF. how can i how can i stop it from automatically adding the
sheet
name?

thanks,

You're welcome!

--
Biff
Microsoft Excel MVP


"waldorf" wrote in message
...
it actually resolves to "Option1!H56"
which is what i needed.

thank you very much!

"T. Valko" wrote:

=INDIRECT("'" & B13)&"!"&"H56"
B13 contains the sheet name i am referencing

Try it like this:

=INDIRECT("'"&B13&"'!H56")

Resolves to: =Summary!H56

--
Biff
Microsoft Excel MVP


"waldorf" wrote in message
...
i have used this formula before (with 2003) without problems.
=INDIRECT("'" & B13)&"!"&"H56"
i am using excel 2007 now.
when i step through the "Evaluate Formula", the result on the side
shows
the
cell reference with the workseet name.

"Summary!$B$13" (B13 contains the sheet name i am referencing)
the result is "Summary!Option1!H56 which is not valid.
i need it to drop the current worksheet (Summary).
i can't find a setting or option to tell it not to do this.

i hope this makes more sense.


"Gord Dibben" wrote:

Assume we can't see your worksheet and formula you are attempting to
create
using the INDIRECT function.

Now try a re-post with that information.


Gord Dibben MS Excel MVP

On Tue, 26 Aug 2008 05:57:01 -0700, waldorf
wrote:

i am referencing a worksheet using the indirect function.
excel is automatically adding the active worksheet name to the
formula
which
returns #REF. how can i how can i stop it from automatically adding
the
sheet
name?

thanks,