Set REF# by Cell

ok I have a workbook with sheets named by month (Sep, Oct, Nov...) I have a
sheet named Weekly. In Weekly I have a LOOKUP function as such.

=LOOKUP(B1,Oct!$B$4:$B$34,Oct!$E$4:$E$34)

B1 = Date in Weekly to lookup.
Oct! = the sheet to look in.
I'm looking for a way to have the Oct referance be determined via cell
referance. I tried some codes but just can't get this to work. I know I
would do a

TEXT(B1,"mmm") to get "Oct" trouble is setting up my original code to do
this. I've also been tryingto do it with INDIRECT function but to also no
result. I know it's just a wrong " or , somewhere.

One way:

=LOOKUP(B1,INDIRECT("'" & C1 & "'!B4:B34"),INDIRECT("'" & C1 &
"'!E4:E34"))

Note that the single quotes are only necessary if C1 may have a space in
the text.


In article <[email protected]>,
"Mike Punko" <[email protected]> wrote:

> ok I have a workbook with sheets named by month (Sep, Oct, Nov...) I have a
> sheet named Weekly. In Weekly I have a LOOKUP function as such.
>
> =LOOKUP(B1,Oct!$B$4:$B$34,Oct!$E$4:$E$34)
>
> B1 = Date in Weekly to lookup.
> Oct! = the sheet to look in.
> I'm looking for a way to have the Oct referance be determined via cell
> referance. I tried some codes but just can't get this to work. I know I
> would do a
>
> TEXT(B1,"mmm") to get "Oct" trouble is setting up my original code to do
> this. I've also been tryingto do it with INDIRECT function but to also no
> result. I know it's just a wrong " or , somewhere.

Thanks man. I knew it was somethign simple I wsa forgetting the single quotes.

"JE McGimpsey" wrote:

> One way:
>
> =LOOKUP(B1,INDIRECT("'" & C1 & "'!B4:B34"),INDIRECT("'" & C1 &
> "'!E4:E34"))
>
> Note that the single quotes are only necessary if C1 may have a space in
> the text.
>
>
> In article <[email protected]>,
> "Mike Punko" <[email protected]> wrote:
>
> > ok I have a workbook with sheets named by month (Sep, Oct, Nov...) I have a
> > sheet named Weekly. In Weekly I have a LOOKUP function as such.
> >
> > =LOOKUP(B1,Oct!$B$4:$B$34,Oct!$E$4:$E$34)
> >
> > B1 = Date in Weekly to lookup.
> > Oct! = the sheet to look in.
> > I'm looking for a way to have the Oct referance be determined via cell
> > referance. I tried some codes but just can't get this to work. I know I
> > would do a
> >
> > TEXT(B1,"mmm") to get "Oct" trouble is setting up my original code to do
> > this. I've also been tryingto do it with INDIRECT function but to also no
> > result. I know it's just a wrong " or , somewhere.
>