Help with an Indirect formula to reference sheet names

Well I figured the simple part of this formula formula. However, I'm having
trouble fitting
it into a larger scale formula. The simple part is to reference a Worksheet
name based on a cells contents. I want to reference "Sheet" & the number
which resides in cell AH1. The formula below seems to do it.

="Sheet"& ROW(INDIRECT(AH1&":"&$AH$1))

I'm not sure if that is proper method, but it seems to work.

Now I need to fit that into a larger formula, but I'm having trouble. Here
is the current formula.

=RIGHT('Sheet1'!A1,(LEN('Sheet1'!A1)-(LEN(AF1)+40)))

Where it shows "Sheet1", I need to fit in the Indirect formula that I listed
above.
Any ideas?

Thanks,
Paul


"Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
news:983994DC-63B9-400B-9FC8-9F0B82BEA756@microsoft.com...
> You are looking for INDIRECT... Since you have used it before I will not
> go
> into the details...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "PCLIVE" wrote:
>
>> I've done this before, but I can't remember how. I need to reference a
>> sheet number based on another cell.
>>
>> For example:
>> ='Sheet1'!A1+A2
>>
>> In my situation, I need to reference the Sheet number where the number
>> will
>> be in a cell. Let's say that the number "1" is in A5. In my formula
>> above
>> I need something that will say Sheet & A2 which will be "Sheet1".
>>
>> How can I do this?
>>
>> Thanks,
>>
>>
>>

I couldn't get your working part to work for me, but I think that this is
what you want

=RIGHT(INDIRECT(AH1&"!A1"),LEN(INDIRECT(AH1&"!A1"))-(LEN(AF1)+40))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"PCLIVE" <pclive@livenet.net> wrote in message
news:uLC%23ZpPmFHA.3448@TK2MSFTNGP12.phx.gbl...
> Well I figured the simple part of this formula formula. However, I'm
having
> trouble fitting
> it into a larger scale formula. The simple part is to reference a
Worksheet
> name based on a cells contents. I want to reference "Sheet" & the number
> which resides in cell AH1. The formula below seems to do it.
>
> ="Sheet"& ROW(INDIRECT(AH1&":"&$AH$1))
>
> I'm not sure if that is proper method, but it seems to work.
>
> Now I need to fit that into a larger formula, but I'm having trouble.
Here
> is the current formula.
>
> =RIGHT('Sheet1'!A1,(LEN('Sheet1'!A1)-(LEN(AF1)+40)))
>
> Where it shows "Sheet1", I need to fit in the Indirect formula that I
listed
> above.
> Any ideas?
>
> Thanks,
> Paul
>
>
> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> news:983994DC-63B9-400B-9FC8-9F0B82BEA756@microsoft.com...
> > You are looking for INDIRECT... Since you have used it before I will not
> > go
> > into the details...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "PCLIVE" wrote:
> >
> >> I've done this before, but I can't remember how. I need to reference a
> >> sheet number based on another cell.
> >>
> >> For example:
> >> ='Sheet1'!A1+A2
> >>
> >> In my situation, I need to reference the Sheet number where the number
> >> will
> >> be in a cell. Let's say that the number "1" is in A5. In my formula
> >> above
> >> I need something that will say Sheet & A2 which will be "Sheet1".
> >>
> >> How can I do this?
> >>
> >> Thanks,
> >>
> >>
> >>
>
>
>
>

I tried your suggestion but it did not work for me.

Maybe I can explain my working part a little better.
This is the exact formula.

="Week "& ROW(INDIRECT(AH1&":"&$AH$1))

Assume that the number "3" is in cell AH1.
The formula result (at least for me) is "Week 3".

Now, in the larger scale formula, I'm trying to reference a worksheet that
was determined by that formula.
I'm not sure how I would do this. It's possible that your suggestion would
work with some alteration, but I'm not sure where to start.
Any further suggestions?

Thanks for your help,
Paul


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23ZQTXJQmFHA.3256@TK2MSFTNGP12.phx.gbl...
>I couldn't get your working part to work for me, but I think that this is
> what you want
>
> =RIGHT(INDIRECT(AH1&"!A1"),LEN(INDIRECT(AH1&"!A1"))-(LEN(AF1)+40))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "PCLIVE" <pclive@livenet.net> wrote in message
> news:uLC%23ZpPmFHA.3448@TK2MSFTNGP12.phx.gbl...
>> Well I figured the simple part of this formula formula. However, I'm
> having
>> trouble fitting
>> it into a larger scale formula. The simple part is to reference a
> Worksheet
>> name based on a cells contents. I want to reference "Sheet" & the number
>> which resides in cell AH1. The formula below seems to do it.
>>
>> ="Sheet"& ROW(INDIRECT(AH1&":"&$AH$1))
>>
>> I'm not sure if that is proper method, but it seems to work.
>>
>> Now I need to fit that into a larger formula, but I'm having trouble.
> Here
>> is the current formula.
>>
>> =RIGHT('Sheet1'!A1,(LEN('Sheet1'!A1)-(LEN(AF1)+40)))
>>
>> Where it shows "Sheet1", I need to fit in the Indirect formula that I
> listed
>> above.
>> Any ideas?
>>
>> Thanks,
>> Paul
>>
>>
>> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
>> news:983994DC-63B9-400B-9FC8-9F0B82BEA756@microsoft.com...
>> > You are looking for INDIRECT... Since you have used it before I will
>> > not
>> > go
>> > into the details...
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "PCLIVE" wrote:
>> >
>> >> I've done this before, but I can't remember how. I need to reference
>> >> a
>> >> sheet number based on another cell.
>> >>
>> >> For example:
>> >> ='Sheet1'!A1+A2
>> >>
>> >> In my situation, I need to reference the Sheet number where the number
>> >> will
>> >> be in a cell. Let's say that the number "1" is in A5. In my formula
>> >> above
>> >> I need something that will say Sheet & A2 which will be "Sheet1".
>> >>
>> >> How can I do this?
>> >>
>> >> Thanks,
>> >>
>> >>
>> >>
>>
>>
>>
>>
>
>

You can get that result simply with

="Week " & AH1

Is Week 3 a worksheet name? if so, is this closer

=RIGHT(INDIRECT("'Week "&AH1&"'!A1"),LEN(INDIRECT(("'Week
"&AH1&"'!A1"))-(LEN(AF1)+40))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"PCLIVE" <pclive@livenet.net> wrote in message
news:ui9MmVQmFHA.1996@TK2MSFTNGP10.phx.gbl...
> I tried your suggestion but it did not work for me.
>
> Maybe I can explain my working part a little better.
> This is the exact formula.
>
> ="Week "& ROW(INDIRECT(AH1&":"&$AH$1))
>
> Assume that the number "3" is in cell AH1.
> The formula result (at least for me) is "Week 3".
>
> Now, in the larger scale formula, I'm trying to reference a worksheet that
> was determined by that formula.
> I'm not sure how I would do this. It's possible that your suggestion
would
> work with some alteration, but I'm not sure where to start.
> Any further suggestions?
>
> Thanks for your help,
> Paul
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23ZQTXJQmFHA.3256@TK2MSFTNGP12.phx.gbl...
> >I couldn't get your working part to work for me, but I think that this is
> > what you want
> >
> > =RIGHT(INDIRECT(AH1&"!A1"),LEN(INDIRECT(AH1&"!A1"))-(LEN(AF1)+40))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "PCLIVE" <pclive@livenet.net> wrote in message
> > news:uLC%23ZpPmFHA.3448@TK2MSFTNGP12.phx.gbl...
> >> Well I figured the simple part of this formula formula. However, I'm
> > having
> >> trouble fitting
> >> it into a larger scale formula. The simple part is to reference a
> > Worksheet
> >> name based on a cells contents. I want to reference "Sheet" & the
number
> >> which resides in cell AH1. The formula below seems to do it.
> >>
> >> ="Sheet"& ROW(INDIRECT(AH1&":"&$AH$1))
> >>
> >> I'm not sure if that is proper method, but it seems to work.
> >>
> >> Now I need to fit that into a larger formula, but I'm having trouble.
> > Here
> >> is the current formula.
> >>
> >> =RIGHT('Sheet1'!A1,(LEN('Sheet1'!A1)-(LEN(AF1)+40)))
> >>
> >> Where it shows "Sheet1", I need to fit in the Indirect formula that I
> > listed
> >> above.
> >> Any ideas?
> >>
> >> Thanks,
> >> Paul
> >>
> >>
> >> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
> >> news:983994DC-63B9-400B-9FC8-9F0B82BEA756@microsoft.com...
> >> > You are looking for INDIRECT... Since you have used it before I will
> >> > not
> >> > go
> >> > into the details...
> >> > --
> >> > HTH...
> >> >
> >> > Jim Thomlinson
> >> >
> >> >
> >> > "PCLIVE" wrote:
> >> >
> >> >> I've done this before, but I can't remember how. I need to
reference
> >> >> a
> >> >> sheet number based on another cell.
> >> >>
> >> >> For example:
> >> >> ='Sheet1'!A1+A2
> >> >>
> >> >> In my situation, I need to reference the Sheet number where the
number
> >> >> will
> >> >> be in a cell. Let's say that the number "1" is in A5. In my
formula
> >> >> above
> >> >> I need something that will say Sheet & A2 which will be "Sheet1".
> >> >>
> >> >> How can I do this?
> >> >>
> >> >> Thanks,
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

Thanks a bunch Bob.
I got it working. Your suggestion was Sooo close. I used your suggested
formula, but I had to remove a left parenthesis.

Your suggestion:
=RIGHT(INDIRECT("'Week "&AH1&"'!A1"),LEN(INDIRECT(("'Week
"&AH1&"'!A1"))-(LEN(AF1)+40))

On first look, I noticed the double set of Parenthesis - (("'Week
"&AH1&"'!A1"))
Additionally, when I copy and pasted the formula, Excel wanted to
correct it by adding a right parenthesis. I didn't pay it any mind at the
time.
When it didn't work, through trial and error, I found the extra set of
parenthesis and remove it.

The working formula is:
=RIGHT(INDIRECT("'Week "&AH1&"'!A1"),LEN(INDIRECT("'Week
"&AH1&"'!A1"))-(LEN(AF1)+40))

Everything works great now. I couldn't have done it without your help.

Thanks again,
Paul


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uHx$dpQmFHA.3256@tk2msftngp13.phx.gbl...
> You can get that result simply with
>
> ="Week " & AH1
>
> Is Week 3 a worksheet name? if so, is this closer
>
> =RIGHT(INDIRECT("'Week "&AH1&"'!A1"),LEN(INDIRECT(("'Week
> "&AH1&"'!A1"))-(LEN(AF1)+40))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "PCLIVE" <pclive@livenet.net> wrote in message
> news:ui9MmVQmFHA.1996@TK2MSFTNGP10.phx.gbl...
>> I tried your suggestion but it did not work for me.
>>
>> Maybe I can explain my working part a little better.
>> This is the exact formula.
>>
>> ="Week "& ROW(INDIRECT(AH1&":"&$AH$1))
>>
>> Assume that the number "3" is in cell AH1.
>> The formula result (at least for me) is "Week 3".
>>
>> Now, in the larger scale formula, I'm trying to reference a worksheet
>> that
>> was determined by that formula.
>> I'm not sure how I would do this. It's possible that your suggestion
> would
>> work with some alteration, but I'm not sure where to start.
>> Any further suggestions?
>>
>> Thanks for your help,
>> Paul
>>
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:%23ZQTXJQmFHA.3256@TK2MSFTNGP12.phx.gbl...
>> >I couldn't get your working part to work for me, but I think that this
>> >is
>> > what you want
>> >
>> > =RIGHT(INDIRECT(AH1&"!A1"),LEN(INDIRECT(AH1&"!A1"))-(LEN(AF1)+40))
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "PCLIVE" <pclive@livenet.net> wrote in message
>> > news:uLC%23ZpPmFHA.3448@TK2MSFTNGP12.phx.gbl...
>> >> Well I figured the simple part of this formula formula. However, I'm
>> > having
>> >> trouble fitting
>> >> it into a larger scale formula. The simple part is to reference a
>> > Worksheet
>> >> name based on a cells contents. I want to reference "Sheet" & the
> number
>> >> which resides in cell AH1. The formula below seems to do it.
>> >>
>> >> ="Sheet"& ROW(INDIRECT(AH1&":"&$AH$1))
>> >>
>> >> I'm not sure if that is proper method, but it seems to work.
>> >>
>> >> Now I need to fit that into a larger formula, but I'm having trouble.
>> > Here
>> >> is the current formula.
>> >>
>> >> =RIGHT('Sheet1'!A1,(LEN('Sheet1'!A1)-(LEN(AF1)+40)))
>> >>
>> >> Where it shows "Sheet1", I need to fit in the Indirect formula that I
>> > listed
>> >> above.
>> >> Any ideas?
>> >>
>> >> Thanks,
>> >> Paul
>> >>
>> >>
>> >> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message
>> >> news:983994DC-63B9-400B-9FC8-9F0B82BEA756@microsoft.com...
>> >> > You are looking for INDIRECT... Since you have used it before I will
>> >> > not
>> >> > go
>> >> > into the details...
>> >> > --
>> >> > HTH...
>> >> >
>> >> > Jim Thomlinson
>> >> >
>> >> >
>> >> > "PCLIVE" wrote:
>> >> >
>> >> >> I've done this before, but I can't remember how. I need to
> reference
>> >> >> a
>> >> >> sheet number based on another cell.
>> >> >>
>> >> >> For example:
>> >> >> ='Sheet1'!A1+A2
>> >> >>
>> >> >> In my situation, I need to reference the Sheet number where the
> number
>> >> >> will
>> >> >> be in a cell. Let's say that the number "1" is in A5. In my
> formula
>> >> >> above
>> >> >> I need something that will say Sheet & A2 which will be "Sheet1".
>> >> >>
>> >> >> How can I do this?
>> >> >>
>> >> >> Thanks,
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>