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