Variable within a formula

[Guest]

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?

 

[Pete_UK]

Try this:

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

Hope this helps.

Pete

 

[Guest]

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

 

[Pete_UK]

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

Pete

 

[Harlan Grove]

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.