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!

Originally Posted by jdriscol

That works, thanks! Do you know why the address formula doesn't work in this scenario?

Because ADDRESS returns a text string. Your formula would work with INDIRECT added:

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

However, INDIRECT is a volatile function while INDEX isn't.