DB

[smittycft99]

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

[Aladin Akyurek]

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

[smittycft99]

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: ***Is this in Database***


First input for NutritionLOG is
Food carbs proteins fats
A13 C13 D13 E13

Database: A2:J963

[smittycft99]

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: ***Is this in Database***


First input for NutritionLOG is
Food carbs proteins fats
A13 C13 D13 E13

Database: A2:J963