Reusing grouping of non-adjacent cells

[shumaker]

I'll explain what I'm conceptually trying to accomplish, then how I
have tried and failed.

I would like to be able to use ctrl+clicking to select non-adjacent
cells in a column, then reuse that set in other formulas for doing
things like averages or sums of columns.

For example, I ctrl click in column B to select cells in rows 1,2,3,
and 5. I want to maybe do a sum in column C for those rows, and maybe
average in column D for those rows.

I tried giving them a name, then doing soemthing like

Sum(Offset(NamedSet,0,1))

But this gives #Value error as soon as Offset is evaluated.

I just want to be able to redefine the set of rows that the other
formulas should operate on, and have that cascade to the other
formulas. Most of them are sum formulas, so even a solution with sum
formulas would be nice.

I was thinking of adding a column of checkboxes and somehow use the
sumif formula, but the checkboxes are kinda funny in the way they
"float" around and don't seem to actually reside inside a cell.

 

[Guest]

The problem is OFFSET().

OFFSET() does not work for disjoint ranges.

Some functions like AVERAGE() and SUM() will work for disjoint ranges.
Some functions like OFFSET() and COUNTIF() will not.

You get to decide if this is a bug or an un-advertised deficiency.

 

[JimMay]

In a standard Module enter:

Sub Foo()
Range("C1").Value = WorksheetFunction.Sum(Selection)
Range("D1").Value = WorksheetFunction.Average(Selection)
End Sub

Then Click on several of your numbers (while holding
Down the Control key, as you say) that are in Column B

Then Run Foo

Hope this helps,
Jim May