I have a table that has a set of values (formated MMM123) and their
corresponding numbers(formated 123). I would like to reference data from
the corresponing sheet number(sheetname='123') based on the the original
value(MMM123). What this would all do is when looking up a cell in another
sheet(VIEWDATA) it would check the table to find which number sheet('123')
and then display data found in in a cell on that sheet. What would be the
best way to go about doing this?

Hi James,

James Michael wrote:
I have a table that has a set of values (formated MMM123) and their
corresponding numbers(formated 123). I would like to reference data
from the corresponing sheet number(sheetname='123') based on the the
original value(MMM123). What this would all do is when looking up a
cell in another sheet(VIEWDATA) it would check the table to find
which number sheet('123') and then display data found in in a cell on
that sheet. What would be the best way to go about doing this?

I'm not exactly clear on what you're trying to do. But take a look at the
INDIRECT worksheet function in help - I think it may do what you're looking
to do.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

To break it down, I am wondering if you can use a cell's contents to
determine the name of a sheet to reference. If the cell says 1, then it
loads sheet '1'. I could not get the range function to do what I want yet,
but I am stil working with it.

Thanks

"Jake Marx" wrote:

Hi James,

James Michael wrote:
I have a table that has a set of values (formated MMM123) and their
corresponding numbers(formated 123). I would like to reference data
from the corresponing sheet number(sheetname='123') based on the the
original value(MMM123). What this would all do is when looking up a
cell in another sheet(VIEWDATA) it would check the table to find
which number sheet('123') and then display data found in in a cell on
that sheet. What would be the best way to go about doing this?

I'm not exactly clear on what you're trying to do. But take a look at the
INDIRECT worksheet function in help - I think it may do what you're looking
to do.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Perhaps I am missing something but when I try to use your formula it says
that it is an "invalid referance to an external worksheet..." Am I missing
something to change? I have a sheet named "1" and A1 contains the value 1.

Thanks Again

"Tom Ogilvy" wrote:

Sounds like Jake nailed it:

=indirect("'" & A1 & "'!B9)

if there is a 1 in A1 this cell will retrieve the value in

'1'!B9

it is the same as the formula

='1'!B9

--
Regards,
Tom Ogilvy

"James Michael" wrote in message
...
To break it down, I am wondering if you can use a cell's contents to
determine the name of a sheet to reference. If the cell says 1, then it
loads sheet '1'. I could not get the range function to do what I want
yet,
but I am stil working with it.

Thanks

"Jake Marx" wrote:

Hi James,

James Michael wrote:
I have a table that has a set of values (formated MMM123) and their
corresponding numbers(formated 123). I would like to reference data
from the corresponing sheet number(sheetname='123') based on the the
original value(MMM123). What this would all do is when looking up a
cell in another sheet(VIEWDATA) it would check the table to find
which number sheet('123') and then display data found in in a cell on
that sheet. What would be the best way to go about doing this?

I'm not exactly clear on what you're trying to do. But take a look at
the
INDIRECT worksheet function in help - I think it may do what you're
looking
to do.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

except for a typo where the last double quote was omitted, it worked fine
for me in the situation you described. Here is the correction:

=INDIRECT("'" & A1 & "'!B9")

Copied right out of the formula bar where the cell was displaying the value
from '1'!B9



--
Regards,
Tom Ogilvy

"James Michael" wrote in message
...
Perhaps I am missing something but when I try to use your formula it says
that it is an "invalid referance to an external worksheet..." Am I
missing
something to change? I have a sheet named "1" and A1 contains the value
1.

Thanks Again

"Tom Ogilvy" wrote:

Sounds like Jake nailed it:

=indirect("'" & A1 & "'!B9)

if there is a 1 in A1 this cell will retrieve the value in

'1'!B9

it is the same as the formula

='1'!B9

--
Regards,
Tom Ogilvy

"James Michael" wrote in
message
...
To break it down, I am wondering if you can use a cell's contents to
determine the name of a sheet to reference. If the cell says 1, then
it
loads sheet '1'. I could not get the range function to do what I want
yet,
but I am stil working with it.

Thanks

"Jake Marx" wrote:

Hi James,

James Michael wrote:
I have a table that has a set of values (formated MMM123) and
their
corresponding numbers(formated 123). I would like to reference
data
from the corresponing sheet number(sheetname='123') based on the
the
original value(MMM123). What this would all do is when looking up
a
cell in another sheet(VIEWDATA) it would check the table to find
which number sheet('123') and then display data found in in a cell
on
that sheet. What would be the best way to go about doing this?

I'm not exactly clear on what you're trying to do. But take a look
at
the
INDIRECT worksheet function in help - I think it may do what you're
looking
to do.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]