Which formula would work for this series? ..Help needed

[Guest]

I have two worksheets.

Worksheet1 is an input sheet
Worksheet2 is a data query reference sheet. The data on worksheet2 is as
follows:

Program(col1) Donor (col2)
01001 6970
01001 9901
01012 8933
01013 9696
01013 8933
01013 7120


I have defined a list called Donors for Column 2 of Worksheet2

On Worksheet1, cell B5, when a user enters in a Program number, I want Excel
to reference worksheet2 and list all available donors associated with the
program. I want this list as a drop down in worksheet1 cell A17.

For example: User enters 01001 in cell B5, they must choose a donor from
the drop down list, excel reviews the table and brings back 6970, 9901 for
them to choose in cell A17.

***Donors are not one-to-one relationship with the program. A donor can
fund more than one program.

I have tried the IFSUM command but continue to get errors. The LOOKUP
command does work but only brings over ONE value instead of all values
associated with the program.

Is there a formula, command to achieve what I am looking for?

 

[Biff]

Hi!

You'd have to extract the donors based on the program selected then use that
extracted list as the source for the drop down.

Here's a sample file with this implemented:

http://s49.yousendit.com/d.aspx?id=03UOM0B1GJ82O24IVB4CH4GK0D

Biff

 

[Guest]

biff,

This is pointing me to the right direction...however I do have questions:

=IF(ROWS($1:1)<=COUNTIF(I$4:I$647,'Budget
Input'!B$5),INDEX(J$4:J$647,SMALL(IF(I$4:I$647='Budget
Input'!B$5,ROW(J$4:J$647)-ROW(J$4)+1),ROWS($1:1))),"")

this is the actual code that I will be using....I am able to populate the
first donor value on the cacluated field but not the second.

If I edit =IF(ROWS($1:1) to =IF(ROWS($1:2) I get a #NUM! value. Also in
your script and mine I do not see the refence to A17 (the output where the
user chooses the drop down list)

Thanks for the info....

 

[Biff]

=IF(ROWS($1:1)<=COUNTIF(I$4:I$647,'Budget

Input'!B$5),INDEX(J$4:J$647,SMALL(IF(I$4:I$647='Budget
Input'!B$5,ROW(J$4:J$647)-ROW(J$4)+1),ROWS($1:1))),"")

this is the actual code that I will be using....I am able to populate the
first donor value on the cacluated field but not the second.

The formula is an array formula. In the sample file select cell Sheet2 E2.
In the formula bar you'll notice that the formula is enclosed in braces
{..........}. These braces denote that it is an array.

When you type this formula in a cell instead of just hitting the ENTER key
like you normally would you MUST use the key combination of
CTRL,SHIFT,ENTER. That is, hold down both the CTRL key and the SHIFT key
then hit ENTER. When done properly Excel will place the braces around the
formula. You MUST use the key combo to do this. You can't just type the
braces in. Also, anytime you edit an array formula you must re-enter it as
an array using the key combo.

Also in your script and mine I do not see the
refence to A17 (the output where the
user chooses the drop down list)

In the sample file select cell Sheet1 A17
Goto Data>Validation
You'll see that the source for the drop down list is: =Donors
I used that name as suggested in your post.

Donors is a defined dynamic range based on the extracted donor numbers list
in Sheet2 column E.

Goto Insert>Name>Define
You'll see the name Donors listed
Select Donors
In the Refers to box you'll see the formula that is used to create the
dynamic range.

A couple of notes:

The formula to extract the donor numbers has to be copied to enough cells so
that it returns all the possible matches. For example, if the maximun number
of donors for any one program is 15 then you need to copy the formula to at
least 15 cells.

Also, the donor numbers you posted were 4 digit numeric values without
leading 0's. So, the dynamic named formula is based on that data being
numeric. If some of the data has leading 0's, unless you specifically use a
custom format to use leading 0's, Excel treats entries with leading 0's as
TEXT. If that's the case the named formula will need to be changed.

Biff

 

[Guest]

Biff,

YES!!!!!!!!!!!!!!!!!! The ctrl+shift was part of the problem. Once I
'entered' that correctly I was able to see the calculated list when B5 was
chosen.

I only have 8 possible Donors per Program so instead of the OFFSET
calculation in Validation I did a simple range of the calculation.

Thanks for the heads up on the leading zeros all donors are 4 char and some
have a 0 as the leading character but the data is configured on the sheet as
text and has no problems!!!!!

Thanks again