INDIRECT reference to another worksheet in same workbook

Sorry for a dumb question but I cannot get this right.

I have a workbook with multiple worksheets, lets say the sheet names are
Summary, Jan, Feb etc. In sheet 'Summary' in cell A1 I have the name of one
of these sheets, let's say A1 =Jan.

In cell A3 (of 'Summary') i want the contents of cell B2 of sheet Jan
displayed.

I have the following formula in cell A3 (of 'Summary') =INDIRECT(A1&"!B2")
but I get a REF error back.

Please help

johnc
Thu Jul 24 07:50:01 CDT 2008

It works fine for me. Have you verified there isn't an error in cell B2 of Jan?
--
John C


"Johan" wrote:

> Sorry for a dumb question but I cannot get this right.
>
> I have a workbook with multiple worksheets, lets say the sheet names are
> Summary, Jan, Feb etc. In sheet 'Summary' in cell A1 I have the name of one
> of these sheets, let's say A1 =Jan.
>
> In cell A3 (of 'Summary') i want the contents of cell B2 of sheet Jan
> displayed.
>
> I have the following formula in cell A3 (of 'Summary') =INDIRECT(A1&"!B2")
> but I get a REF error back.
>
> Please help

Teethlessmama
Thu Jul 24 07:50:03 CDT 2008

It worked on my machine

"Johan" wrote:

> Sorry for a dumb question but I cannot get this right.
>
> I have a workbook with multiple worksheets, lets say the sheet names are
> Summary, Jan, Feb etc. In sheet 'Summary' in cell A1 I have the name of one
> of these sheets, let's say A1 =Jan.
>
> In cell A3 (of 'Summary') i want the contents of cell B2 of sheet Jan
> displayed.
>
> I have the following formula in cell A3 (of 'Summary') =INDIRECT(A1&"!B2")
> but I get a REF error back.
>
> Please help

demechanik
Thu Jul 24 07:52:00 CDT 2008

Data inconsistency is the usual culprit. Eg there could be an extra trailing
white space in the sheetname entered in B2 (not readily visible) which is
throwing the match off

Try this rendition in A3: =INDIRECT("'"&TRIM(A1)&"'!B2")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Johan" wrote:
> Sorry for a dumb question but I cannot get this right.
>
> I have a workbook with multiple worksheets, lets say the sheet names are
> Summary, Jan, Feb etc. In sheet 'Summary' in cell A1 I have the name of one
> of these sheets, let's say A1 =Jan.
>
> In cell A3 (of 'Summary') i want the contents of cell B2 of sheet Jan
> displayed.
>
> I have the following formula in cell A3 (of 'Summary') =INDIRECT(A1&"!B2")
> but I get a REF error back.
>
> Please help

Johan
Thu Jul 24 08:05:00 CDT 2008

Thanks for the response. I was trying to simplify my problem here. I now
realise that my problem is that I actually have a vlookup formula in A1 (and
not simply a directly the sheet name). This is probably causing my problem

"Teethless mama" wrote:

> It worked on my machine
>
> "Johan" wrote:
>
> > Sorry for a dumb question but I cannot get this right.
> >
> > I have a workbook with multiple worksheets, lets say the sheet names are
> > Summary, Jan, Feb etc. In sheet 'Summary' in cell A1 I have the name of one
> > of these sheets, let's say A1 =Jan.
> >
> > In cell A3 (of 'Summary') i want the contents of cell B2 of sheet Jan
> > displayed.
> >
> > I have the following formula in cell A3 (of 'Summary') =INDIRECT(A1&"!B2")
> > but I get a REF error back.
> >
> > Please help