I am trying to replace the number portion of a cell reference with the
results of another formula. I can't seem to get it to work.

This is the formula I started with:
=SUM(WO!J2:J5679)

But instead of the 5679, I want to use the result of the following formula:

=ROWS(WO!qry_WO)-1

where qry_WO is a named range on the WO sheet.

I can do this using a macro, but would prefer to do it on the worksheet.

Is this even possible?

Try this:

=SUM(INDIRECT("WO!J2:J"&ROWS(WO!qry_WO)-1))

Hope this helps.

Pete

On Aug 20, 11:04 pm, Tom Melosi <Tom
wrote:
I am trying to replace the number portion of a cell reference with the
results of another formula. I can't seem to get it to work.

This is the formula I started with:
=SUM(WO!J2:J5679)

But instead of the 5679, I want to use the result of the following formula:

=ROWS(WO!qry_WO)-1

where qry_WO is a named range on the WO sheet.

I can do this using a macro, but would prefer to do it on the worksheet.

Is this even possible?

Thank you Pete, that is exactly what I was looking for!

"Pete_UK" wrote:

Try this:

=SUM(INDIRECT("WO!J2:J"&ROWS(WO!qry_WO)-1))

Hope this helps.

Pete

On Aug 20, 11:04 pm, Tom Melosi <Tom
wrote:
I am trying to replace the number portion of a cell reference with the
results of another formula. I can't seem to get it to work.

This is the formula I started with:
=SUM(WO!J2:J5679)

But instead of the 5679, I want to use the result of the following formula:

=ROWS(WO!qry_WO)-1

where qry_WO is a named range on the WO sheet.

I can do this using a macro, but would prefer to do it on the worksheet.

Is this even possible?

Thanks for feeding back, Tom - glad to be of help.

Pete

On Aug 20, 11:14 pm, Tom Melosi
wrote:
Thank you Pete, that is exactly what I was looking for!



"Pete_UK" wrote:
Try this:

=SUM(INDIRECT("WO!J2:J"&ROWS(WO!qry_WO)-1))

Hope this helps.

Pete

On Aug 20, 11:04 pm, Tom Melosi <Tom
wrote:
I am trying to replace the number portion of a cell reference with the
results of another formula. I can't seem to get it to work.

This is the formula I started with:
=SUM(WO!J2:J5679)

But instead of the 5679, I want to use the result of the following formula:

=ROWS(WO!qry_WO)-1

where qry_WO is a named range on the WO sheet.

I can do this using a macro, but would prefer to do it on the worksheet.

Is this even possible?- Hide quoted text -

- Show quoted text -

"Pete_UK" wrote...
Try this:

=SUM(INDIRECT("WO!J2:J"&ROWS(WO!qry_WO)-1))
....

Or eliminate the volatile INDIRECT call by using

=SUM(WO!J2:INDEX(WO!$J:$J,ROWS(WO!qry_WO)-1))

which has the added benefit that if rows were added above row 2 or columns
inserted or deleted to the left of col J in the WO worksheet, the
nonvolatile formula wouldn't need to be revised.