Row reference in a formulas
 

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

Row reference in a formulas
 

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

On Jan 11, 3:56*pm, wrote:
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

Row reference in a formulas
 

On Jan 11, 11:15*am, Pete_UK wrote:
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

On Jan 11, 3:56*pm, wrote:



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- Hide quoted text -

- Show quoted text -

Thanks Pete

Row reference in a formulas
 

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

Pete

On Jan 11, 4:44*pm, wrote:
On Jan 11, 11:15*am, Pete_UK wrote:





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

On Jan 11, 3:56*pm, wrote:

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- Hide quoted text -

- Show quoted text -

Thanks Pete- Hide quoted text -

- Show quoted text -