Combine SUM and INDIRECT with chaing sheet names

[miicker]

I would like to combine SUM and INDIRECT to calculate the sum off a specific rang. The sheet name is entered in a cell.

The name of the sheet I wan't to calculate is entered in cell J2. I am using formulas to get the date from that sheet for example:

=INDIRECT("'" & $J$2 & "'!H6")

Gets the value from cell H6 of the sheet entered in cell J2.

I was thinking about this formula:

=INDIRECT(SUM("'" & $J$2 & "'!H6:H17"))

To get the sum from H6 to H16 from the sheet entered in cell J2, but it gives me the error "VALUE"

Is there a formula I can use to accomplish this?

 

[Joe4]

You wrap the SUM around the INDIRECT function, not the other way around like you have it.
Try:

=SUM(INDIRECT("'" & $J$2 & "'!H6:H17"))

 

[miicker]

Thanks, just before reading this I tried it myself and it worked indeed!