Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up a value in a list & returning multiple corresponding va
Complete Subject: Looking up a value in a list & returning multiple
corresponding values in an in-cell dropdown Column A = list of Program numbers Column B = list of Project numbers For a given Program number there can be one or more Project numbers. Consequently, Column A contains duplicate Program numbers. Cell C1 = a user inputs a Program number Cell D1 = I would like to display an in-cell dropdown with the list of Project numbers associated with the inputted Program number in cell C1 I looked at a potential solution I found at http://office.microsoft.com/en-us/ex...1033#backtotop. Using a named range, I can get the results to appear in an in-cell dropdown. However, since the number of results will vary by Program number, I have not been able to figure out how NOT to display "blank" results in the in-cell dropdown. For example, if I create a four-cell named range, but the number of results (i.e., Project numbers) for a given Program number is only two, the in-cell dropdown displays the two Project numbers as well as two blank "placeholders". Any help would be greatly appreciated. Thanks, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up a value in a list & returning multiple correspondingva
Bob wrote:
Complete Subject: Looking up a value in a list & returning multiple corresponding values in an in-cell dropdown Column A = list of Program numbers Column B = list of Project numbers For a given Program number there can be one or more Project numbers. Consequently, Column A contains duplicate Program numbers. Cell C1 = a user inputs a Program number Cell D1 = I would like to display an in-cell dropdown with the list of Project numbers associated with the inputted Program number in cell C1 I looked at a potential solution I found at http://office.microsoft.com/en-us/ex...1033#backtotop. Using a named range, I can get the results to appear in an in-cell dropdown. However, since the number of results will vary by Program number, I have not been able to figure out how NOT to display "blank" results in the in-cell dropdown. For example, if I create a four-cell named range, but the number of results (i.e., Project numbers) for a given Program number is only two, the in-cell dropdown displays the two Project numbers as well as two blank "placeholders". Any help would be greatly appreciated. Thanks, Bob If you make your named ranges /dynamic/ you might get the results you want. A dynamic range can be made to adjust automatically as values are added/removed. This formula in the "refers to" section of the Define Name dialog will point to the values in column A, discounting the first row (assuming it has the column label): =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up a value in a list & returning multiple correspondin
Thanks for your help. Unfortunately, the in-cell dropdown is still showing
blank entries when the results are less than 4 Project numbers, which is the total number of cells I needed to copy the following formula: {=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A $9,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$9,ROW($A$1:$A$7)),ROW(1:1) ),2))} Note: there can be up to 4 Projects for a given Program. I welcome any other suggestions. Thanks again, Bob "smartin" wrote: Bob wrote: Complete Subject: Looking up a value in a list & returning multiple corresponding values in an in-cell dropdown Column A = list of Program numbers Column B = list of Project numbers For a given Program number there can be one or more Project numbers. Consequently, Column A contains duplicate Program numbers. Cell C1 = a user inputs a Program number Cell D1 = I would like to display an in-cell dropdown with the list of Project numbers associated with the inputted Program number in cell C1 I looked at a potential solution I found at http://office.microsoft.com/en-us/ex...1033#backtotop. Using a named range, I can get the results to appear in an in-cell dropdown. However, since the number of results will vary by Program number, I have not been able to figure out how NOT to display "blank" results in the in-cell dropdown. For example, if I create a four-cell named range, but the number of results (i.e., Project numbers) for a given Program number is only two, the in-cell dropdown displays the two Project numbers as well as two blank "placeholders". Any help would be greatly appreciated. Thanks, Bob If you make your named ranges /dynamic/ you might get the results you want. A dynamic range can be made to adjust automatically as values are added/removed. This formula in the "refers to" section of the Define Name dialog will point to the values in column A, discounting the first row (assuming it has the column label): =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up a value in a list & returning multiple correspondin
Ah, I missed a part of what you were trying to do.
Now, I thought I had a good follow-up solution, but in testing it gets really wonky in ways I have never seen before... I see your formula enumerates the relevant Projects based on the selected Program: {=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF( etc... If you have placed your formula in a helper column, say starting at E1, this will return the range of Projects: =OFFSET(Sheet1!E1,0,0,COUNT(IF(LEN(Sheet1!E1:E7)0 ,1))) Two problems with this... One, if you make a named range out of my OFFSET formula, it looks good the first time you check it. Change the Program parameter, then check the named range again -- it has changed! This is what I see after changing the Program parameter once: =OFFSET(Sheet1!IV3,0,0,COUNT(IF(LEN(Sheet1!IV3:IV9 )0,1))) It gets even more fubar on subsequent parameter changes. Second problem is, you can't tell the ActiveX combo to use a named range, nor will it accept the OFFSET formula. Building on what I now know, I would take this to VBA, capturing a change event in whatever cell selects the Program, and populating the Projects in a form combo. Sorry about the goose chase... I hope someone else has insights. Bob wrote: Thanks for your help. Unfortunately, the in-cell dropdown is still showing blank entries when the results are less than 4 Project numbers, which is the total number of cells I needed to copy the following formula: {=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A $9,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$9,ROW($A$1:$A$7)),ROW(1:1) ),2))} Note: there can be up to 4 Projects for a given Program. I welcome any other suggestions. Thanks again, Bob "smartin" wrote: Bob wrote: Complete Subject: Looking up a value in a list & returning multiple corresponding values in an in-cell dropdown Column A = list of Program numbers Column B = list of Project numbers For a given Program number there can be one or more Project numbers. Consequently, Column A contains duplicate Program numbers. Cell C1 = a user inputs a Program number Cell D1 = I would like to display an in-cell dropdown with the list of Project numbers associated with the inputted Program number in cell C1 I looked at a potential solution I found at http://office.microsoft.com/en-us/ex...1033#backtotop. Using a named range, I can get the results to appear in an in-cell dropdown. However, since the number of results will vary by Program number, I have not been able to figure out how NOT to display "blank" results in the in-cell dropdown. For example, if I create a four-cell named range, but the number of results (i.e., Project numbers) for a given Program number is only two, the in-cell dropdown displays the two Project numbers as well as two blank "placeholders". Any help would be greatly appreciated. Thanks, Bob If you make your named ranges /dynamic/ you might get the results you want. A dynamic range can be made to adjust automatically as values are added/removed. This formula in the "refers to" section of the Define Name dialog will point to the values in column A, discounting the first row (assuming it has the column label): =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning multiple values from a list | Excel Discussion (Misc queries) | |||
Sort multiple columns with multiple formulas without returning #R | Excel Worksheet Functions | |||
Returning multiple corresponding values using lookup in a list | Excel Discussion (Misc queries) | |||
Returning next value from a list | Excel Worksheet Functions | |||
Returning list values | Excel Worksheet Functions |