Aladin, please help

[smittycft99]

First off it works amazing, perfect!
Thank you a million!
I hope to impose on you one more time
I do get the reference from the DB in A3 as your code suggests. And my first imput for the menu goes to A14. The problem is people usually have more than 1 item.
let's say I pull up a database reference in A3
and in A14 I say =A3 then vlookup fills in the appropriate columns
But A15 will be the next item, I can't set that to A3 also. How could I do this?

ORIGINAL POST BELOW
=================================
On 2002-05-15 10:24, smittycft99 wrote:
Below is my original post.
The VLookup is great, but my DB is very large and user's will have to type in the Exact phrase into A1 to get B1,C1,D1 filled in. But for A1 the phrase could be long, ie "Rice Crispies treats". Is there a way for the user to type in Rice and then go to a small drop menu showing all entries in db with "rice", I know you can do data validation (and that the DB has to be in the same worksheet) but then the drop menu would be very long. Any ideas, thanks a million in advance

I have a DB:

Food Item Carbs Prot Fats
cereal 45 15 3
milk 20 20 1
bread 45 13 2

Then on another worksheet
when someone types in "cereal" in A1,
Then B1 gets "45", C1 gets "15", D1 gets "3"

Database is in worksheet: database
Menu is in worksheet: Nutrition Log


--------------------------------------------------------------------------------



I'm changing the sheet name Nutrition Log to NutritionLog here.

Sort you database in Database (db) on the column of the food items. The db seems to run from A to E. I'll assume that the actual data start at row 2.

In G2 enter:

=Nutritionlog!A2

In G3 enter:

=MATCH(Nutritionlog!A2&"*",Database!A:A,0)

In G4 enter:

=MATCH(Nutritionlog!A2&"*",Database!A:A)

In G5 enter:

=ROW(Database!A2)

In NutritionLog insert 2 rows before the rows where you show the data associated with the user choice.

In A1 enter: Enter the first word in cell below

Or something to that effect, so that the user what is expected.

Activate Insert|Name|Define.
Enter CurList as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Database!$A$2,Database!$G$3-Database!$G$5,0,Database!$G$4-(Database!$G$3-1),1)

Activate A3.
Activate Data|Validation.
Select List for Allow.
Enter as Source:

=CurList

Activate OK.

I assume there are appropriate look up formulas in B3, C3, and D3.

Aladin

[Aladin Akyurek]

On 2002-05-15 12:42, smittycft99 wrote:
First off it works amazing, perfect!
Thank you a million!
I hope to impose on you one more time
I do get the reference from the DB in A3 as your code suggests. And my first imput for the menu goes to A14. The problem is people usually have more than 1 item.
let's say I pull up a database reference in A3
and in A14 I say =A3 then vlookup fills in the appropriate columns
But A15 will be the next item, I can't set that to A3 also. How could I do this?

You need another first word entry point (one different from the first). Using exactly the same proc I described, you create CurList2 which you use in the cell where you allow to make another choice.

But, be warned, you can't do this indefinitely without incurring performance costs.