Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items in Drp-down List
Hello,
Could someone please help me modify the code below so that it does the following: In my Excel workbook I have a worksheet named: Lookup In cell B13 there is a drop-down list. The drop-down list was created by using Data Validation. The Data Validation criteria is: Allow = List & Source =SCA The code I am referring to is this code: SCA =Offset('SA Register'!$D$3,0,0,COUNTS,('SA Register'!$D$3:$D$65536),1) This formula selects column D on the worksheet named SA Register. It selects from cell D3 to the last cell in column D with data in it. There are no blank cells in column D. Unfortunately, it selects all data in column D. I need it to select only unique items listed in column D. Any help is greatly appreciated, Kind regards, Chris. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items in Drp-down List
Hi Chris
you have to create a helper column which only contains the unique entries. And then refere to this helper column. e.g. enter the following formulas on your 'SA Register' sheet: E3: =D3 E4: Enter the array formula (entered with CTRL+SHIFT+ENTER): =IF(ISNA(MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0) ),"",INDEX($D$3:$D$10 00,MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0))) and copy this down as far as needed After this use nthe following formula for your name definition: SCA =Offset('SA Register'!$E$3,0,0,COUNTA('SA Register'!$E$3:$E$65536)-COUNTBLANK('SA Register'!$E$3:$E$65536),1) -- Regards Frank Kabel Frankfurt, Germany "Chris Hankin" schrieb im Newsbeitrag ... Hello, Could someone please help me modify the code below so that it does the following: In my Excel workbook I have a worksheet named: Lookup In cell B13 there is a drop-down list. The drop-down list was created by using Data Validation. The Data Validation criteria is: Allow = List & Source =SCA The code I am referring to is this code: SCA =Offset('SA Register'!$D$3,0,0,COUNTS,('SA Register'!$D$3:$D$65536),1) This formula selects column D on the worksheet named SA Register. It selects from cell D3 to the last cell in column D with data in it. There are no blank cells in column D. Unfortunately, it selects all data in column D. I need it to select only unique items listed in column D. Any help is greatly appreciated, Kind regards, Chris. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items in Drp-down List
Hello Frank,
Thanks very much for your help. I am a little confused with the Helper column that you want me to create. Do I need to insert another column next to column E? I am not exactly sure where I am supposed to enter in the following formula: =IF(ISNA(MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0) ),"",INDEX($D$3:$D$10 00,MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0))) Please advise where exactly this formula is meant to go. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items in Drp-down List
Hi
if your current list in in column D you may use column E as a helper column (or any other column you want). Now insert the first formula in E3 and the second one in cell E4. After this copy the formula from E4 to all other cells in column E for as many rows you need -- Regards Frank Kabel Frankfurt, Germany "Chris Hankin" schrieb im Newsbeitrag ... Hello Frank, Thanks very much for your help. I am a little confused with the Helper column that you want me to create. Do I need to insert another column next to column E? I am not exactly sure where I am supposed to enter in the following formula: =IF(ISNA(MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0) ),"",INDEX($D$3:$D$10 00,MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0))) Please advise where exactly this formula is meant to go. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items in Drp-down List
Don't forget to point the SCA name at the new column RE as Frank said in his
original reply. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Frank Kabel" wrote in message ... Hi if your current list in in column D you may use column E as a helper column (or any other column you want). Now insert the first formula in E3 and the second one in cell E4. After this copy the formula from E4 to all other cells in column E for as many rows you need -- Regards Frank Kabel Frankfurt, Germany "Chris Hankin" schrieb im Newsbeitrag ... Hello Frank, Thanks very much for your help. I am a little confused with the Helper column that you want me to create. Do I need to insert another column next to column E? I am not exactly sure where I am supposed to enter in the following formula: =IF(ISNA(MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0) ),"",INDEX($D$3:$D$10 00,MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0))) Please advise where exactly this formula is meant to go. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items in Drp-down List
Hello Frank, Thanks again for all your help - much appreciated. I entered in all your formalas and receieved the following Excel warnings: I inserted a Helper column in column E and entered the following formula in E3 and copied down to the end of my list: =IF(ISNA(MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0) ),"",INDEX($D$3:$D$100 0,MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0))) Excel warned that it could not calculate the formula. Cell references in the formula refer to the formulas results, creating a circular reference. I changed the Name Definition of SCA to: SCA =Offset('SA Register'!$E$3,0,0,COUNTA('SA Register'!$E$3:$E$65536)-COUNTBLANK('SA Register'!$E$3:$E$65536),1) Excel warns that the source currently evaluates to an error. Could you please help as I do not know how to fix this. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items in Drp-down List
Hi Chris
as mentioned in my previopus post, put the following in the cells in column E: Cell E3: =D3 and in Cell E4 (not in cell E3) the formulas from below! -- Regards Frank Kabel Frankfurt, Germany Chris Hankin wrote: Hello Frank, Thanks again for all your help - much appreciated. I entered in all your formalas and receieved the following Excel warnings: I inserted a Helper column in column E and entered the following formula in E3 and copied down to the end of my list: =IF(ISNA(MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0) ),"",INDEX($D$3:$D$10 0 0,MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0))) Excel warned that it could not calculate the formula. Cell references in the formula refer to the formulas results, creating a circular reference. I changed the Name Definition of SCA to: SCA =Offset('SA Register'!$E$3,0,0,COUNTA('SA Register'!$E$3:$E$65536)-COUNTBLANK('SA Register'!$E$3:$E$65536),1) Excel warns that the source currently evaluates to an error. Could you please help as I do not know how to fix this. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items in Drp-down List
Hello Frank,
I am sorry about this going back & forth over this Excel problem, but I am a newbie - so please do not be upset - I am trying to learn as best I can. In cell E3 I entered =D3. I selected cell E4 and then entered in your formula: =IF(ISNA(MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0) ),"",INDEX($D$3:$D$100 0,MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0))) in the formula bar. I then pressed Ctrl+Shift+Enter as it is an array formula. Excel warns me that there is an error in the formula. I read the Excel help section on entering array formulas and got nowhere. Am I doing the right thing? Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items in Drp-down List
Hi Chris
no problem :-) this formula works for me. One idea: You also use an english Excel version and you also use the coma (and not the semicolon) as separator?. If this is not the problem you may email me your file and I'll insert the formula for you: email: frank[dot]kabel[at]freenet[dot]de -- Regards Frank Kabel Frankfurt, Germany Chris Hankin wrote: Hello Frank, I am sorry about this going back & forth over this Excel problem, but I am a newbie - so please do not be upset - I am trying to learn as best I can. In cell E3 I entered =D3. I selected cell E4 and then entered in your formula: =IF(ISNA(MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0) ),"",INDEX($D$3:$D$10 0 0,MATCH(0,COUNTIF($E$3:$E3,$D$3:$D$1000),0))) in the formula bar. I then pressed Ctrl+Shift+Enter as it is an array formula. Excel warns me that there is an error in the formula. I read the Excel help section on entering array formulas and got nowhere. Am I doing the right thing? Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tagging unique items in a list | Excel Worksheet Functions | |||
Function to count unique items in list | Excel Worksheet Functions | |||
VBA to get List of Unique Items from column | Excel Programming | |||
VBA to get List of Unique Items from column | Excel Programming | |||
VBA to get List of Unique Items from column | Excel Programming |