I am trying to SUM a range of cells in another sheet within the same worksheet/workbook.

The formula I want is =SUM(Ph!BP15:Ph!BW15)

However, to future proof this formula, since the BP:BW range may change at some point, I created a Named Range called "Gutters" which is BP:BW in the Sheet "Ph".

The row I need to reference is contained in cell B1 of the current sheet I am writing the formula in. So the formula I have now is:

=sum(address(B1,COLUMN(gutters),4,true,"Ph")&":"&address(B1,column(gutters)+COLUMNS(gutters)-1,4,true,"Ph"))

Where B1 contains a row number, and "Ph" is the name of another sheet in the worksheet.

I am using COLUMN(gutters) to tell me the number of the first column of the named range Gutters, because I am trying to sum all of the cells in row 15 within the named range Gutters.

Using:

address(B1,COLUMN(gutters),4,true,"Ph")&":"&address(B1,column(gutters)+COLUMNS(gutters)-1,4,true,"Ph")

returns exactly the text I want, "Ph!BP15:Ph!BW15", so I figured I could put it within a SUM function and it would sum that range. But it actually returns the number 0. There are numbers in all those cells, so I am puzzled why it's returning 0. It does not give me an error. If I simply put =SUM(Ph!BP15:Ph!BW15) it returns the correct sum of 179. So why isn't it returning 179 when I derive it using the address functions separated by &":"& ?

I also tried adding INDIRECT( to each address function and this still returned 0.

Any help?