Using a cell reference to populate the sheet reference in a formula

[Jasesair]

I have sheets that are all identical in their structure, and I have a cell that is returning the appropriate sheet name. I'm wanting to refer to this cell in a formula to lookup the sheet name with the corresponding cell reference in that chosen sheet.

To give a real example, the sheet names are 1Units, 2Units, 3Units, 4Units, and the appropriate one is now returned in cell E33. My end result is grabbing required info with an Index Match. Here is what I'm chasing (but knowing I haven't displayed the formula correctly to join the sheet reference with the cell/s in that sheet). Any assistance to correct would be fantastic.

=index(E33!A13:A23,match(P5,E33!B13:B23))

 

[BobUmlas]

=INDEX(INDIRECT(E33&"!A13:A23"),MATCH(P5,INDIRECT(E33&"!B13:B23"),0))

 

[Jeffrey Mahoney]

I believe you're going to have to do something like this:
=INDEX(INDIRECT("'"&E33&"'!A13:A23"),MATCH(P5,INDIRECT("'"&E33&"'!B13:B23")))

 

[Jeffrey Mahoney]

I always put the apostrophe at the beginning and end of the sheet name in case it has a space in the name

 

[BobUmlas]

I would too, but he said:
To give a real example, the sheet names are 1Units, 2Units, 3Units, 4Unit

 

[Jasesair]

I might try and better explain and give my actual example (mainly so I don't confuse myself!).

I currently have this formula working fine:
=(INDEX(INDIRECT($M$36&"!g63:bq63"),MATCH(M$38,INDIRECT($M$36&"!$g$59:$bq$59"),0))

The horrible INDIRECT formula cannot be dragged down or across so I was looking for a way to avoid INDIRECT.

I now have this working properly, but it's long:
=INDEX(CHOOSE($m$36,PrepUnits!$G63:$BQ63,'1Units'!$G63:$BQ63,'2Units'!$G63:$BQ63,'3Units'!$G63:$BQ63,'4Units'!$G63:$BQ63,'5Units'!$G63:$BQ63,'6Units'!$G63:$BQ63),MATCH(M$38,CHOOSE($m$36,PrepUnits!$G$59:$BQ$59,'1Units'!$G$59:$BQ$59,'2Units'!$G$59:$BQ$59,'3Units'!$G$59:$BQ$59,'4Units'!$G$59:$BQ$59,'5Units'!$G$59:$BQ$59,'6Units'!$G$59:$BQ$59),0))

Not that it matters, but I had to adjust m36 to allow this CHOOSE function to work.

I was hoping to reduce a lot of the clutter in the formula to have a new cell (E33) produce the sheet name that's happening above...with the thinking that the resulting formula would replace the above and look a little something like this:...(I've bolded it because I know it's wrong)
=INDEX($E$33!$G$63:$BQ$63,MATCH(M$38,E33!$G$59:$BQ$59))

It seems like something Excel would support happening.