Use INDIRECT to calculate a sheetname

  • Thread starter Thread starter Matthew White
  • Start date Start date
M

Matthew White

Hi,

I am trying to use INDIRECT to calculate a sheet name. I can do it
separatly, like this:

=INDIRECT((E5-1)&"!E5")

This returns, for example, 2 (which is a valid sheet name).

If I try to incorporate this into an OFFSET function, I have trouble. Here
is the OFFSET before I mess with it:

=OFFSET('2'!R$12,MATCH(LARGE('2'!AW$12:AW$91,ROW()-ROW(Q$12)+1),'2'!AW$12:AW
$91,0)-1,0)

I want to use the INDIRECT idea to replace the '2'! part of this function.
I've tried this:

=OFFSET(('&INDIRECT((E5-1)&"!E5")&'!R$12),MATCH(LARGE('2'!AW$12:AW$91,ROW()-
ROW(Q$12)+1),'2'!AW$12:AW$91,0)-1,0)

So far no luck. All I get is a "#Value" error.

Any ideas?

Thanks,

Matt
 
Hi again,

I found a way to two-step this. If I use helper cells to build the sheet
and range reference, like this:

R94 contains:
="'"&E5-1&"'"&"!$R$12"
and
R95 contains:
="'"&E5-1&"'"&"!AW$12:AW$91"

Then use a simple INDIRECT in the OFFSET function:

=OFFSET(INDIRECT($R$94),MATCH(LARGE(INDIRECT($R$95),ROW()-ROW(Q$12)+1),INDIR
ECT($R$95),0)-1,0)

it works. I still wonder if there is way to do this without the helper
cells....
 
Back
Top