Help with an Indirect formula to reference sheet names

  • Thread starter Thread starter PCLIVE
  • Start date Start date
P

PCLIVE

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
 
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)
 
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
 
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)
 
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
 
Back
Top