Formula to reference sheet number base on cell

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 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,
>
>
>

Well I figured out the simple formula. However, I'm having trouble fitting
it into a larger scale formula.

Here is what I've got.
="Sheet"& ROW(INDIRECT(AH1&":"&$AH$1))

I'm not sure if that is exactly right, but it works.

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" <[email protected]> wrote in message
news:[email protected]...
> 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,
>>
>>
>>