Variable within a formula

T

Tom Melosi

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?
 
H

Harlan Grove

Pete_UK said:
Try this:

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

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.
 
Top