Using cell content to access another worksheet in same workbook

This should be simple but the functions that would appear to be the answer
don't work. I have a worksheet name that is contained in column one of
another worksheet row. I want to use that cell content to access the named
worksheet and populate a cell in the same row of the first worksheet. For
example, in worksheet A the content of cell A2 = B. I want to populate cell
B2 in worksheet A with the contents of cell B200 in worksheet B (the content
of A!A2). Then for other contents of column A of sheet A, Bx will be
populated with the contents of B200 of the sheet named in Ax where x is the
row number. The INDIRECT function does not appear to work in this case but
maybe I am formatting it wrong.

Thanks

indirect only works if the worksheet is OPEN.

You'll need to use indirect.ext contained within the MOREFUNC addin and will
need the full file path.

http://xcell05.free.fr/english/index...func_Functions

"ScubaBum" wrote:

> This should be simple but the functions that would appear to be the answer
> don't work. I have a worksheet name that is contained in column one of
> another worksheet row. I want to use that cell content to access the named
> worksheet and populate a cell in the same row of the first worksheet. For
> example, in worksheet A the content of cell A2 = B. I want to populate cell
> B2 in worksheet A with the contents of cell B200 in worksheet B (the content
> of A!A2). Then for other contents of column A of sheet A, Bx will be
> populated with the contents of B200 of the sheet named in Ax where x is the
> row number. The INDIRECT function does not appear to work in this case but
> maybe I am formatting it wrong.
>
> Thanks
>

=INDIRECT("'"&A2&"'!B200")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"ScubaBum" <u23313@uwe> wrote in message news:62289de2ea2fc@uwe...
> This should be simple but the functions that would appear to be the answer
> don't work. I have a worksheet name that is contained in column one of
> another worksheet row. I want to use that cell content to access the named
> worksheet and populate a cell in the same row of the first worksheet. For
> example, in worksheet A the content of cell A2 = B. I want to populate
cell
> B2 in worksheet A with the contents of cell B200 in worksheet B (the
content
> of A!A2). Then for other contents of column A of sheet A, Bx will be
> populated with the contents of B200 of the sheet named in Ax where x is
the
> row number. The INDIRECT function does not appear to work in this case but
> maybe I am formatting it wrong.
>
> Thanks

My apologies. I misread the question.

"ScubaBum" wrote:

> This should be simple but the functions that would appear to be the answer
> don't work. I have a worksheet name that is contained in column one of
> another worksheet row. I want to use that cell content to access the named
> worksheet and populate a cell in the same row of the first worksheet. For
> example, in worksheet A the content of cell A2 = B. I want to populate cell
> B2 in worksheet A with the contents of cell B200 in worksheet B (the content
> of A!A2). Then for other contents of column A of sheet A, Bx will be
> populated with the contents of B200 of the sheet named in Ax where x is the
> row number. The INDIRECT function does not appear to work in this case but
> maybe I am formatting it wrong.
>
> Thanks
>

Bob,

That was quick and it worked. Mind explaining how you constructed this answer?
IE, what does each part of the function mean, particularly the quotes,
ampersands, etc?

Thanks

Bob Phillips wrote:
>=INDIRECT("'"&A2&"'!B200")
>
>--
>
>HTH
>
>Bob Phillips
>
>(replace xxxx in the email address with gmail if mailing direct)
>
>> This should be simple but the functions that would appear to be the answer
>> don't work. I have a worksheet name that is contained in column one of
>[quoted text clipped - 8 lines]
>>
>> Thanks

is there a function that works in a similar way but can reference files
that are not open?

Thanks


--
BRABUS