Row reference in a formulas

[DonFlak]

I'm sure this is pretty simply but for whatever reason the old grey
matter is not functioning.

I have a formula

=IF(D20="","",SUMIF($O$8:$O$33,$A$5,E$8:E$33))

The adjeacent cells have similiar formulas

=IF(G20="","",SUMIF($O$8:$O$33,$A$5,H$8:H$33))
=IF(J20="","",SUMIF($O$8:$O$33,$A$5,K$8:K$33))
=IF(M20="","",SUMIF($O$8:$O$33,$A$5,N$8:N$33)) etc...you get the point

In any event, weekly the row number in the first reference "the 20"
needs to change. This reference is built in to 40 or so formulas so I
don't want to have to manually change each.

Is there a way I can enter the row number somewhere and have the
formula pick up the correct row number from my entry.

For instance, I would like to go to page 1 and enter 38 in cell A1

I would like all of the formulas with the reference to 20 to change to
38 so in the first formula it would now read

=IF(D38="","",SUMIF($O$8:$O$33,$A$5,E$8:E$33))

simply based on my entry.

Can this be done and if so, how?

Thanks
Don

 

[Pete_UK]

If you use A1 in the same sheet, then try this:

=IF(INDIRECT("D"&$A$1)="","",SUMIF($O$8:$O$33,$A$5,E$8:E$33))

If it is in a different sheet then you will need to include the sheet
name before $A$1.

Hope this helps.

Pete

 

[DonFlak]

If you use A1 in the same sheet, then try this:

=IF(INDIRECT("D"&$A$1)="","",SUMIF($O$8:$O$33,$A$5,E$8:E$33))

If it is in a different sheet then you will need to include the sheet
name before $A$1.

Hope this helps.

Pete











- Show quoted text -

Thanks Pete

 

[Pete_UK]

You're welcome, Don - thanks for feeding back.

Pete