Using ADDRESS as part of another function

Hi,

I am struggling with a formula I'm trying to use. I would like it to be something along the lines of:

=SUM('Sheet3'!C47 : (ADDRESS(47, Period + 2, 1, 1, "Sheet3")))

where the address function is returning a certain row with a moving column value (depending on my formula variable called "Period"). However, this is returning an error.

When I copy the "(ADDRESS(47, Period + 2, 1, 1, "Sheet3"))" portion into another cell, it returns the value " 'Sheet3'!$G$47 ". When I type my original function in with the following:

=SUM('Sheet3'!C47:'Sheet3'!$G$47)

that works, too, but for some reason not the combination of ADDRESS and SUM. Please help!