Re: Look up sheet name determined in cell

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <JamesMichael@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:47040A1A-3DFD-4FA1-8E93-CD2733D64CA5@xxxxxxxxxxxxxxxx
> 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" <JamesMichael@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
> > news:9B0EE593-A852-42EB-9859-FD3897BE717E@xxxxxxxxxxxxxxxx
> > > 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]
> > > >
> > > >
> >
> >
> >


.

Relevant Pages