Referencing cells within the Sumproduct function in Excel

I 've got a formula that needs to be copied horizontally while the references shift up one spot with each spot I shift to the right. The formula is straightforward:

=IFERROR(SUMPRODUCT(AY34:AY44,D34:D44)/SUM(D34:D44),1)

As I shift to the right the formula shifts as follows:

=IFERROR(SUMPRODUCT(AZ33:AY43,E33:E43)/SUM(E33:E43),1)

The pattern continues. The column reference takes care of itself, but the row reference needs an n-1 adjustment.

I thought I could use index or indirect nested within the sumproduct function by creating a list of reference cells:

=IFERROR(SUMPRODUCT(INDIRECT("A1"):AY43,E33:E43)/SUM(E33:E43),1) where the contents of cell A1 is "AZ33". 

Excel does not seem to see INDIRECT("A1") as AZ33 though if I put that function by itself in a cell it does return the desired value.

I figure my syntax is wrong, or there is a better function to use. I tried INDEX() in place of INDIRECT() but excel doesn't see that either.

Can anyone explain this one to me like I am 5?

First, let's establish a way to create a number that decreases when the formula is dragged to the right, starting with 34:

=ROW(A34)-COLUMN(A1)+1

Use this approach in an INDEX() function to reference a cell in that row in column A:

=INDEX($A:$A,ROW(A34)-COLUMN(A1)+1)

That takes care of the start of the range you want to feed to Sumproduct. You can then extend the range returned by the Index() with the range operator : and hard wire the end of the range, say $A$43 and use that inside the Sumproduct()

=SUMPRODUCT(INDEX($A:$A,ROW(A34)-COLUMN(A1)+1):$A$43)

In my example, I have anchored column A with $ signs to be absolute references, but just because I was too lazy to add more data to my test file. See in the screenshot how the formula in column J is now referencing E1, i.e. a relative column reference works, too.