• 413K Members
• 7,787 Online
• 472K Conversations

New Contributor

# Need a quick nudge in the right direction

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).

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

# Re: Need a quick nudge in the right direction

Hi

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:

`SUM(INDEX(B:B,ROW()-\$M\$7):INDEX(B:B,ROW()))`

# Re: Need a quick nudge in the right direction

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

# Re: Need a quick nudge in the right direction

I have a try like this

`=SUM(OFFSET(\$B\$1,ROW()-\$M\$7-1,0,\$M\$7+1,1))`

Perhaps, it could be

`=SUM(OFFSET(\$B\$1,ROW(B1)-ROW(\$B\$1),0,\$M\$7+1,1))`
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