Forum Discussion
Need a quick nudge in the right direction
Hi All, Ive spent 3 nights trying to solve a formula, and I need a pointer. =B5*(($M$6+$M$11)+($M$9*SUM(ADDRESS((ROW()-$M$7),2,4):ADDRESS((ROW()),2,4)))) Formula returns #VALUE! result. The error...
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()))
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))
- Thanks mate, this looks much simpler than my efforts and it worked perfectly
