ShogunPatch
Board Regular
- Joined
- May 8, 2009
- Messages
- 52
I am trying to create a simple formula using the SUM function combined with OFFSET and named ranges as follows
=SUM(OFFSET([named range],,2)
This appears to work fine as long as my named ranges are contiguous, i.e. (B3:B5) for example; however, as soon as a named range contains non-contiguous cells, e.g. (B6,B9) I get a #VALUE! error.
I have searched online and found discussions on (and in some cases workaround solutions for) similar problems (from which I gather I simply have to accept that there are limitations to using OFFSET with non-contiguous ranges), but none that I have found deal specifically with my use of the (I would have thought relatively simple) SUM function so I was wondering whether anyone could suggest a simple solution or workaround.
I would attach a very simple sample worksheet demonstrating the problem if I could simply work out how to attach things to this post, but I can't, so hopefully the above narrative is sufficiently clear...
Many thanks.
=SUM(OFFSET([named range],,2)
This appears to work fine as long as my named ranges are contiguous, i.e. (B3:B5) for example; however, as soon as a named range contains non-contiguous cells, e.g. (B6,B9) I get a #VALUE! error.
I have searched online and found discussions on (and in some cases workaround solutions for) similar problems (from which I gather I simply have to accept that there are limitations to using OFFSET with non-contiguous ranges), but none that I have found deal specifically with my use of the (I would have thought relatively simple) SUM function so I was wondering whether anyone could suggest a simple solution or workaround.
I would attach a very simple sample worksheet demonstrating the problem if I could simply work out how to attach things to this post, but I can't, so hopefully the above narrative is sufficiently clear...
Many thanks.