Need a quick nudge in the right direction

Jeff Kerwin
New Contributor

Hi All,

Ive spent 3 nights trying to solve a formula, and I need a pointer.


Formula returns #VALUE! result.

The error seems to arise in the bolded section above.

If I break each section down, eg, I insert =ADDRESS((ROW()-$M$7),2,4) into its own cell I get the correct result for this portion of the overall formula.  It gives the result of a cell reference (B10 in this example)

If I insert ADDRESS((ROW()),2,4)) into another cell it gives me the right result (B35 in this example).

When I add these two components together in a SUM formula it falls apart SUM(ADDRESS((ROW()-$M$7),2,4):ADDRESS((ROW()),2,4))


In the bold section I am trying to arrive at the result of SUM(B10:B35).


Cell formating is all general numbers


Where am I gong wrong?

3 Replies



ADDRESS() returns a text and not a reference. To convert a text into a reference use INDIRECT().


However, because INDIRECT() is a volatile function I would suggest:



Thanks mate, this looks much simpler than my efforts and it worked perfectly

I have a try like this


Perhaps, it could be

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies