Formula to define non-contiguous range?

[Paul Martin]

Hi folks

Is it possible to use an Excel formula to define a non-contiguous
range? I have a column of data, let's say

A1: Mechanical Causes
A2: blah
A3: blah
A4: Mechanical Causes
A5: blah
A6: Mechanical Causes

I'd like to create a named range that uses a formula that identifies
the cells in a column that have a particular value - in this case
"Mechanical Causes". I suspect it's not possible, but thought I'd see
if anyone else has any ideas.

Thanks in advance

Paul Martin
Melbourne, Australia

 

[T. Valko]

How do you intend to use this named range in a formula?

The formula would have to be able to handle non-contiguous references and
there are very few functions that will do that and the ones that do only
handle numbers.

 

[Shane Devenshire]

Hi,

Please explain what you are trying to do.

 

[T. Valko]

I guess my asking:

How do you intend to use this named range in a formula?

Wasn't sufficient?

 

[Paul Martin]

My intention was to use a formula in a named range, not a named range
in a formula. I think my problem is too complex for Excel formulas,
so I'm working on a VBA solution which is much easier.

By defining a range of non-contiguous data, I want to loop through the
cells in each area, capture (both column and row) offsets which are
then used elsewhere. I have this working, but in a different way to
originally intended.

I'm analysing the values columns, a column offset of Column A, using

Set rngValues = rngCols.SpecialCells(xlCellTypeConstants, 1)

This captures various Areas in the desired columns and, because of the
nature of the data, any values I require are part of single-cell
Areas. I loop through each Area, if the Area.Cells.Count=1, then I
capture offsets of that Area for other values I'm looking for.

Thanks for your responses

Paul

 

[T. Valko]

My intention was to use a formula in a named range

You won't be able to use worksheet functions to create a contiguous range
from non-contiguous references.

 

[Paul Martin]

You won't be able to use worksheet functions to create a contiguous range
from non-contiguous references.

I wasn't trying to. I was trying to define a range of non-contiguous
cells.

 

[T. Valko]

Well, your subject line says:

Formula to define non-contiguous range?

So I was thinking you wanted to use some type of INDEX or OFFSET formula to
create the range.