Round 2 of VLOOKUP command - can the output result in a list?

[ssciarrino]

I have worksheet1 that references worksheet2. (Worksheet2 is a data link to a
table on SQL server0

On Field B5 of Worksheet1 when a user enters in a project number, I want the
VLOOKUP command to go to Worksheet 2 that has a small table (column 1 -
Project, column 2 - Donor) and retrieve ALL values in worksheet2.column2.

I want them to provide a dropdown list in Field B5 of the referenced funders
on worksheet2.column2.

Is this possible?

 

[L. Howard Kittle]

I may not understand full but, try this.

On sheet 2 name the list of donors, say "Donors"
On sheet 1 select B5 then under Data > Valadation > > Setteing > List > and
in the source box enter =Donors > OK. This is your Donors drop down list.
In the cell you want to return the sum of the donor selected enter

=SUMIF(Donors,B5,Sheet2!B1:B7)

In B5 select a donor.

HTH
Regards,
Howard

 

[ssciarrino]

Howard what do you mean by

In the source box enter =Donors > 0K ?

If I enter this expression I get an error....I thought you cannot reference
other sheets in a Data/Validation command?

 

[ssciarrino]

I understand what you say with the =Donors by defining a list in the second
worksheet but this doesn't work.

The second worksheet has two columns, the project and the multiple donors

01001 6970
01001 9910
01001 9901

When I try to Validate the column I get an error saying the validation list
must be delimited (which it cannot be done or at least I know not how to do
this because it is a SQL data link) or one column list.

If you have any other suggestions I am open to it.

Recap, user enters a project in cell B5, on Cell A17 I want to have the list
of donors associated with the project in B5 that correspond to the Worksheet2
data source. worksheet2 data source lists project and their funders (two
column list)

 

[L. Howard Kittle]

You can reference a list on another sheet by naming it. Select only the
column of donors and name it. If you have multiple donors in the column on
sheet 2 you may want to make a unique donors list somewhere, maybe on sheet
1 somewhere, and then there is no need to name the column on sheet 2.

If you do name the list on sheet 2, then you should be okay as long as it is
1 column.

If you want, send me an example workbook and I will look at it. I may not
have the whole concept down as well as I should.

Regards,
Howard

 

[ssciarrino]

I think I am getting there but still need help on the SUMIF equation.

This is what I did.

Worksheet 2 has the following:

Project Donor
01001 6970
01001 8933
01034 9901
01034 6970

I created a List called 'Donors' for Worksheet2.Donor column (column 2)

Now on my Worksheet1 on the Donor field where I want the drop down I
indicate in validation the list.

All is well....now the trick part.

User enters in a project in Worksheet1.A17, once the project is entered I
want the appropriate donors associated with the project on worksheet2.donors
list to appear as a drop down on Worksheet1.B5.

As per Howard Kittle I try to use this formula

=SUMIF(Range,Criteria,Sum_Range)

where Range = the list name 'Donors' from Worksheet2
where Criteria = the project cell Worksheet1.A17
where Sum_Range = the project AND donor column from Worksheet2

When I hit enter I get the following error:
The formula you have typed contains an error....

Almost there just need a bit more help!

Thanks again

 

[L. Howard Kittle]

I think the problem may be that I thought the column "Doners" is to the left
of the "Amount" column. Is this true?

An example worksheet would be helpful, if you would send me one I will give
it a go.

Explain on the sheet what you want to happen.

Regards,
Howard