| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation list with array formulaes
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation list with array formulaes
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")) -- Biff Microsoft Excel MVP "Vinod" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation list with array formulaes
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 "T. Valko" wrote: 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")) -- Biff Microsoft Excel MVP "Vinod" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation list with array formulaes
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"),"HN3 05-"&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"),INDE X($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 "Vinod" wrote: 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 "T. Valko" wrote: 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")) -- Biff Microsoft Excel MVP "Vinod" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation list with array formulaes
Also, if you can use this in column C
=IF(ROWS(C$1:C1)<=COUNTIF($A$1:$A$11,"HN305"),"HN3 05-"&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. "JMB" wrote: 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"),"HN3 05-"&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"),INDE X($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 "Vinod" wrote: 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 "T. Valko" wrote: 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")) -- Biff Microsoft Excel MVP "Vinod" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation list with array formulaes
You might be interested in this.
=IF(ROWS(C$1:C1)<=COUNTIF($A$1:$A$11,"HN305"),INDE X($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"),INDE X($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. -- Biff Microsoft Excel MVP "JMB" wrote in message ... 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"),"HN3 05-"&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"),INDE X($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 "Vinod" wrote: 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 "T. Valko" wrote: 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")) -- Biff Microsoft Excel MVP "Vinod" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Validation list with array formulaes
That does make sense - thanks for the pointer
"T. Valko" wrote: You might be interested in this. =IF(ROWS(C$1:C1)<=COUNTIF($A$1:$A$11,"HN305"),INDE X($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"),INDE X($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. -- Biff Microsoft Excel MVP "JMB" wrote in message ... 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"),"HN3 05-"&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"),INDE X($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 "Vinod" wrote: 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 "T. Valko" wrote: 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")) -- Biff Microsoft Excel MVP "Vinod" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data validation list / named array constant | Excel Worksheet Functions | |||
| Validation list via dynamic array in VBA | Excel Discussion (Misc queries) | |||
| data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
| data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
| Creating array formulaes in macro | Excel Discussion (Misc queries) | |||
![Vinod[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
