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" wrote in message
...
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,

Help with an Indirect formula to reference sheet names
 

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" wrote in message
...
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" wrote in message
...
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,

Help with an Indirect formula to reference sheet names
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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,

Help with an Indirect formula to reference sheet names
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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,

Help with an Indirect formula to reference sheet names
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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,