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

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.


--
Biff
Microsoft Excel MVP


"Paul Martin" wrote in message
...
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

Hi,

Please explain what you are trying to do.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Paul Martin" wrote:

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

I guess my asking:

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

Wasn't sufficient?

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Please explain what you are trying to do.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Paul Martin" wrote:

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

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

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.

--
Biff
Microsoft Excel MVP


"Paul Martin" wrote in message
...
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

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.

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.

--
Biff
Microsoft Excel MVP


"Paul Martin" wrote in message
...

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.