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