Looking up a value in a list & returning multiple corresponding va

[Bob]

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/excel/HA012260381033.aspx?pid=CL100570551033#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

 

[smartin]

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/excel/HA012260381033.aspx?pid=CL100570551033#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)

 

[Bob]

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]

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.