Validation list with array formulaes

[Vinod]

Hi All,

Is it possible to give array formulae as source reference for a cell
datavalidation list?

Note: using formulae
Eg: {=IF(UPPER(A1:A10)=UPPER("HN305"),INDEX(A1:A10,ROW(A1:A10)),"")}

ColA ColB
HN305 HN305-1
HN305 HN305-2
HN306 HN306-1
HN307 HN307-1
HN305 HN305-3
HN306 HN306-2
HN306 HN306-3
HN307 HN307-2
HN307 HN307-3
HN306 HN306-4

I tried with this formulae:
=IF(UPPER(A1:A10)=UPPER("HN305"),INDEX(A1:A10,ROW(A1:A10)),"")

When I've give it in datavalidation list soure and clicked on OK button I
got the message "The source currently evaluates to an error. Do you want to
continue?". If I click on YES nothing is dispalyed in dropdown list.

Is there any another logic to be displayed in dropdown which are
corresponding 'HN305'?

Please help me out.

Advanced Thanks,
Vinod

 

[T. Valko]

Don't you want to use the values in column B for the selections in the drop
down list?

If you can sort your data on column A so that it looks like this:

......A...............B......
HN305.HN305-1
HN305.HN305-2
HN305.HN305-3
HN306.HN306-1
HN306.HN306-2
HN306.HN306-3
HN306.HN306-4
HN307.HN307-1
HN307.HN307-2
HN307.HN307-3

Then, a the source for the drop down:

=OFFSET(B1,MATCH("HN305",A1:A10,0)-1,,COUNTIF(A1:A10,"HN305"))

 

[Vinod]

Thanks Valko for your response,

I'm looking for, without sorting the data, i.e., on fly data will be added.

Right now I'm using filter on column 'A' and copying visible cells in column
'B' to temperory sheet for doing advanced filte. After that building unique
list and calling another function to update validation list.

But this procees is time consuming. Thats why I'm looking for a formulae for
validation list.

Note: It won't have any duplicates in column 'B'.

Is there any workaround for this?

Advanced Thanks,
Vinod

 

[JMB]

I usually use a helper column.

It appears you are incrementing the last value for each occurence of your
data in Column A. If this pattern is true for all of your data, you could
use this formula in C1 and copy down to C10 (non-array entered).

=IF(ROWS(C$1:C1)<=COUNTIF($A$1:$A$11,"HN305"),"HN305-"&ROWS(C$1:C1),"")

If your actual data is not that simplistic, you could try this formula in
C1, copied down to C10 (array entered):
=IF(ROWS(C$1:C1)<=COUNTIF($A$1:$A$11,"HN305"),INDEX($B$1:$B$11,SMALL(IF($A$1:$A$11="HN305",ROW($A$1:$A$11)-MIN(ROW($A$1:$A$11))+1,""),ROWS(C$1:C1))),"")

I like to leave a shaded row below my data and include it as part of my
table. Note the formulae above refers to A1:A11. If you add additional info
to your table, you can insert a row at row 11 and the dependent formula
references do not need to be updated to reflect the new table address.

Then go to Insert/Name/Define
Name: MyList
Refers To: =Sheet1!$C$1:INDEX(Sheet1!$C$1:$C$11,
MAX(MATCH("",Sheet1!$C$1:$C$11,0)-1,1))

Change sheet/cell references if needed. For your data validation,
Allow: List
List: MyList

If you add additional items to your table, you'll need to copy the formula
in Column C down. Alternatively, you could try a small event handler to do
this by naming your table "MyTable" (refers to $A$1:$C$11), then right click
on your worksheet tab and select view code, and paste this into the code
window

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("MyTable")) Is Nothing Then _
Exit Sub

Application.EnableEvents = False
With Me.Range("MyList")
.Resize(Me.Range("MyTable").Rows.Count - 1, 1).FillDown
End With
Application.EnableEvents = True

End Sub

 

[JMB]

Also, if you can use this in column C

=IF(ROWS(C$1:C1)<=COUNTIF($A$1:$A$11,"HN305"),"HN305-"&ROWS(C$1:C1),"")

you might re-evaluate the necessity of column B. Instead of harcoding HN305
in the formula you could store HN305 in another cell and just reference that
cell in the formula above.

 

[T. Valko]

You might be interested in this.

=IF(ROWS(C$1:C1)<=COUNTIF($A$1:$A$11,"HN305"),INDEX($B$1:$B$11,SMALL(IF($A$1:$A$11="HN305",ROW($A$1:$A$11)-MIN(ROW($A$1:$A$11))+1,""),ROWS(C$1:C1))),"")

To make that a little more efficient move the offset adjustment outside of
the SMALL function like this:

=IF(ROWS(C$1:C1)<=COUNTIF($A$1:$A$11,"HN305"),INDEX($B$1:$B$11,SMALL(IF($A$1:$A$11="HN305",ROW($A$1:$A$11)),ROWS(C$1:C1))-MIN(ROW($A$1:$A$11))+1),"")

I've recently started using this method.

The logic behind it:

Instead of calculating the offset on the entire array of ROW(....) you just
calculate the offset on the single element returned by SMALL.

Not a significant difference on a small range but it can help on a large
range.

 

[JMB]

That does make sense - thanks for the pointer

You might be interested in this.

=IF(ROWS(C$1:C1)<=COUNTIF($A$1:$A$11,"HN305"),INDEX($B$1:$B$11,SMALL(IF($A$1:$A$11="HN305",ROW($A$1:$A$11)-MIN(ROW($A$1:$A$11))+1,""),ROWS(C$1:C1))),"")

To make that a little more efficient move the offset adjustment outside of
the SMALL function like this:

=IF(ROWS(C$1:C1)<=COUNTIF($A$1:$A$11,"HN305"),INDEX($B$1:$B$11,SMALL(IF($A$1:$A$11="HN305",ROW($A$1:$A$11)),ROWS(C$1:C1))-MIN(ROW($A$1:$A$11))+1),"")

I've recently started using this method.

The logic behind it:

Instead of calculating the offset on the entire array of ROW(....) you just
calculate the offset on the single element returned by SMALL.

Not a significant difference on a small range but it can help on a large
range.