Formula to define non-contiguous range?

P

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

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.
 
P

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

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.
 
P

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top