More arrays and names

[The Cube]

Hi all

I have a workbook containing a single worksheet "Sheet1" and workbook level
defined names as follows:
"Categories", refers to Sheet1!$C$8:$C$19
"Machines", refers to Sheet1!$D$8:$D$19
"CategoryRow", refers to ROW(Categories)-7
"CellPointer" refers to =Sheet1$F8 when that cell is highlighted.
"Test" refers to =(Categories=CellPointer)
"Result" refers to =MMULT(--(CategoryRow>=TRANSPOSE(CategoryRow)),--Test)
(Thanks Harlan!)

In the range Sheet1!$E$8:$E$19 I have a multi-cell array formula:
{=INDEX(Categories,MATCH(CategoryRow,Result,FALSE))}
This displays an array as predicted.

Problem 1:

I tried defining a named formula:
MCResult, refers to =INDEX(Categories,MATCH(CategoryRow,Result,FALSE))
in the hope that it would produce an array result the same as that displayed
by the multi-cell array formula currently residing in Sheet1!$E$8:$E$19
But it did not. It did evaluate, but only to the scalar value equal
(possibly by coincidence) the the first element of the multicell array
formula, ie the content of Sheet1$E$8.

So, any way to get a named formula to produce the array shown in
Sheet1!$E$8:$E$19 ?

Problem 2:

The ultimate intention is that when a different row is selected/active, ie
move CellPointer to $F9, then the array "Result" should update accordingly
as should the dependent array "MCResult". At the moment if I move the
cellpointer to $F9 then nothing happens to the values displayed in
Sheet1!$E$8:$E$19. I am not entirely surprised by this, because nothing has
happened to trigger a recalculation. I suppose that I could force a
recalculation by putting it in a Selection_Change() event, but at the moment
nothing happens even if I hit Control+Alt+F9 (with cell F9 selected).

Help??? Thanks

-Cube

 

[Alan Beban]

Did you array enter the formula?

Alan Beban

 

[The Cube]

Did you array enter the formula?

Hi Alan

There are several array formulae in my post, one of which is entered into
cells in the worksheet, and the others are entered as defined names.

The one entered into cells in the worksheet is the formula
=INDEX(Categories,MATCH(CategoryRow,Result,FALSE))
that is array entered (Control+shift+enter) into cells Sheet1!$E$8:$E$19
I confirm that this is array entered and appears surrounded by {} brackets.

I am not aware of any method of array-entering a formula as a defined name.
Until now all names that I have used that could be interpreted as array
formulae are treated by Excel as array formulae by default. In this
example, names "Test" and "Result" evaluate as arrays without any special
entry treatment. It is only the named formula MCResult that does not appear
to follow this treatment. If you know of a way of forcing Excel to
interpret this as an array formula I should be grateful to know how.

Thanks for taking the trouble.

-Cube

 

[Alan Beban]

My question dealt only with your defined name "McResult". To what cell
does McResult return the scalar value? What steps do you go through to
get McResult to return that scalar value to that cell?

Alan Beban

 

[Harlan Grove]

I have a workbook containing a single worksheet "Sheet1" and workbook level
defined names as follows: ...
"CategoryRow", refers to ROW(Categories)-7

You could avoid hardcoding the 7 by using

=ROW(Categories)-CELL("Row",Categories)+1

"CellPointer" refers to =Sheet1$F8 when that cell is highlighted.

...

So you're using the same name in different rows?

In the range Sheet1!$E$8:$E$19 I have a multi-cell array formula:
{=INDEX(Categories,MATCH(CategoryRow,Result,FALSE))}
This displays an array as predicted.

OK, but it's doing too much to get the results it gives. This formula expands to

=INDEX($C$8:$C$19,MATCH(ROW($C$8:$C$19)-7,
MMULT(--(ROW($C$8:$C$19)>=TRANSPOSE(ROW($C$8:$C$19))),
--($C$8:$C$19=$F8)),FALSE))

and without proving the following asertion, this always gives the first N cells
evaluating to CellPointer and the remaining cells evaluating to #N/A, where N is
equal to COUNTIF(Categories,CellPointer). It'd be more efficient to enter the
following array formula in Sheet1!E8:E19.

=IF(CategoryRow<=COUNTIF(Categories,CellPointer),CellPointer,#N/A)

Problem 1:

I tried defining a named formula:
MCResult, refers to =INDEX(Categories,MATCH(CategoryRow,Result,FALSE))
in the hope that it would produce an array result the same as that displayed
by the multi-cell array formula currently residing in Sheet1!$E$8:$E$19
But it did not. It did evaluate, but only to the scalar value equal
(possibly by coincidence) the the first element of the multicell array
formula, ie the content of Sheet1$E$8.

So, any way to get a named formula to produce the array shown in
Sheet1!$E$8:$E$19 ?

Calling INDEX with arrays (or multiple cell ranges) as 2nd (or 3rd) argument
gets into a very grey area in Excel's range/array/array formula emantics. If the
1st argument to INDEX is a range, then INDEX *always* returns a range reference
as its result. Even if, e.g., the 2nd argument were 0 and the 3rd argument
{2,3}, INDEX returns an array or range references.

Consider INDEX(A110,0,{2,3}). ROWS(INDEX(A110,0,{2,3})) returns 10, but
COLUMNS(INDEX(A110,0,{2,3})) returns 1!

Anyway, it also depends on where and how you're entering MCResult.

Problem 2:

The ultimate intention is that when a different row is selected/active, ie
move CellPointer to $F9, then the array "Result" should update accordingly
as should the dependent array "MCResult". At the moment if I move the
cellpointer to $F9 then nothing happens to the values displayed in
Sheet1!$E$8:$E$19. I am not entirely surprised by this, because nothing has
happened to trigger a recalculation. I suppose that I could force a
recalculation by putting it in a Selection_Change() event, but at the moment
nothing happens even if I hit Control+Alt+F9 (with cell F9 selected).

This won't work. CellPointer's actual range reference (row-relative) depends on
the row(s) of the cell(s) containing the formula(s) referring to it, not where
the active cell happens to be. That is, if you were in F8 when you defined
CellPointer as $F8, then it's equivelent to the R1C1 reference RC6. If you refer
to it in a formula in cell X99, it'll dereference cell F99 no matter where the
active cell happens to be. That's why any amount of recalculating won't change
your formulas' results.

If you want CellPointer to refer to column F in the same row as the active cell,
then define CellPointer as

=INDIRECT("Sheet1!F"&CELL("Row"))

 

[The Cube]

My question dealt only with your defined name "McResult". To what cell
does McResult return the scalar value? What steps do you go through to
get McResult to return that scalar value to that cell?

Hi Alan, and thank you for the response.

If I array-enter {=McResult} into a single column of cells of the same depth
in rows as the named range "Categories" then it displays the correct array.
But if I enter as normal a formula
=INDEX(McResult,2)
or
=Index(McResult,2,1)
I get #REF!

Ultimately I am trying to get to another array "ItemList:
=OFFSET(McResult,0,0,COUNTIF(McResult,TRUE),1)
But this bombs out as well, although it works if I first array-enter
{=McResult} into a column of cells and then refer ItemList to that column

-Cube

Alan Beban

<rest of post snipped - getting long>

 

[Alan Beban]

I came to this thread (and the previous related one) late, so if my
questions don't seem to advance the problem, feel free to blow them off.
But if "MCResult [which I assume is the same as McResult], refers to
=INDEX(Categories,MATCH(CategoryRow,Result,FALSE))", how does it return
an array? The INDEX function looks like =INDEX(rng,number), which should
return a single value.

Alan Beban

 

[The Cube]

I came to this thread (and the previous related one) late, so if my
questions don't seem to advance the problem, feel free to blow them off.
But if "MCResult [which I assume is the same as McResult], refers to
=INDEX(Categories,MATCH(CategoryRow,Result,FALSE))", how does it return
an array? The INDEX function looks like =INDEX(rng,number), which should
return a single value.

Alan Beban

Thank you Alan. My reasoning, doubtless faulty (I am a relative beginner at
this stuff) was as follows:

It is my self-taught observation that an Excel function that normally takes
the form f(value1) to return value2 will usually produce an array result
array2 when entered as f(array1) (array-entered in cells or simply defined
as a named formula).

The syntax for MATCH() is MATCH(value1,array2,type), returning a single
value2.
In my example, "CategoryRow" and "Result" are both arrays.
I had hoped that MATCH(array1,array2,type) would produce an array comprising
individual values2 of MATCH(value1,array2,type) for each value1 in array1,
and that INDEX(array3,MATCH(array1,array2,type) would produce an array
comprising the results of INDEX(array3,MATCH(value1,array2,type) for each
value1 in array1.
Indeed there is some evidence that it does indeed behave in this manner,
from the fact that if I array-enter that formula into a column of cells of
depth the size of array1 it does behave like an array. It simply will not
return an array whilst it resides as a named formula and indexed
individually.

-Cube

 

[The Cube]

<snip ... a lot of stuff that it is going to take me some time to digest>

It may help if I describe what I am trying to achieve rather than try to
plod along with one micro-query after another.

I am looking to define a named range that can be accessed as a list by data
validation.
The list should contain all (and only) those items contained within the
range "machines" for which the corresponding entry in the range "category"
matches the particular category stated in the cell to the left of the cell
on which data validation operates.
I can achieve this result provided that it is just the one cell that
requires validation, by reference to just the once cell to its immediate
left.
But I want to be able to copy the cell down a column, and for each cell in
the column the category to the left may be different, yet the data
validation must refer to the same named range. Thus:

"Categories" and "machines" may look something like this:

Category Machine
====== =======
Plant Crane
Office Computer
Plant Ladder
Fixtures Carpet
Plant Drill
Office Telephone

Then in another part of the worksheet:

Category Machine
====== ======
Plant ??
Office ??

Data validation on the cells under "Machine" marked by "??" should be by
selection from list, and the item next to Plant should produce a list
Crane
Ladder
Drill

whilst the item next to Office, referring to the same named range, should
produce a list
Computer
Telephone

In my previous post, if I define CellPointer to point ABSOLUTELY to a
particular cell containing a category, and array-enter the INDEX() formula
into cells, and name that array MCResult, then I can get the final list by
naming ItemList refers to:
=OFFSET(MCResult,0,0,COUNTIF(MCResult,TRUE),1)
to strip out all the surplus #N/A!s

That may be a sledgehammer to crack a nut, but it actually works. The
problem that I have been falling down over has been to expand it so that I
can copy the data validation cells down and get it to work by varying the
category.

Thanks for the help Harlan. I shall have another look at your post after I
have had a night's sleep.

-Cube

 

[Harlan Grove]

I am looking to define a named range that can be accessed as a list by data
validation.
The list should contain all (and only) those items contained within the
range "machines" for which the corresponding entry in the range "category"
matches the particular category stated in the cell to the left of the cell
on which data validation operates.
I can achieve this result provided that it is just the one cell that
requires validation, by reference to just the once cell to its immediate
left.

OK, what you want is a list filtered by formulas. That would have to be an
array, and data validation lists won't accept arrays - constant or derived. Data
validation *only* accepts ranges, either hardcoded or derived. This is just
another unfortunate (read: short-sighted) Excel design decision, similar to not
being able to use arrays as first arguments to COUNTIF.

You'll need to use a range to store the first pass at your validation list, and
it must be the same size and shape as Machines and Category ranges. I'll assume
you'll enter it in X8:X19 in the same worksheet as Machines and Category. Enter
the following formulas. Note: I'm assuming the particular category criteria
cells are in column F.

X8 (topmost row):
=INDEX(Machines,MATCH(INDIRECT("F"&CELL("Row")),Category,0))

X9 (2nd row down from top):
=INDEX(Machines,MATCH(INDIRECT("F"&CELL("Row")),
IF((Category=INDIRECT("F"&CELL("Row")))*
(COUNTIF(X$9:X9,Machines)=0),Category),0))

Select X9 and fill down into X10:X19. Define the name InitDVL referring to
X8:X19. Then name your data validation list DVL referring to

=OFFSET(InitDVL,0,0,COUNTIF(InitDVL,"?*"),1)

You'll also need a WorksheetSelectionChange event handler in the ThisWorkbook
class module. It should look like

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range _
)
'--------------------------------------------
Names("InitDVL").RefersToRange.Calculate
End Sub

Then apply DVL as the data validation list range to all the cells for which you
want to use it.

 

[The Cube]

"The Cube" wrote...

<snip ... some advanced stuff that I would never have worked out in a month
of Sundays>

Then apply DVL as the data validation list range to all the cells for which you
want to use it.

Many thanks Harlan. It looks like I was barking up the wrong tree from the
word go and needed hand-holding.

-Cube