[edited] [VLOOKUP] Creating database in Excel - Need help with few sections

Hi there,
Thanks for this amazing platform!

I have an excel database for nutritional information of various food products.
It works like this: There is a scroll down that gives a list of various food items. On selecting a specific food item, its nutritional information pops up in a row, through different columns as shown in screenshot:

Screen Shot 2015-12-30 at 9.55.45 AM.png

These values pops up by looking up in the database on other sheet. (using vlookup)

Now i need help with:
1. I need another scroll down for "quantity" column where user can select quantity of food he has taken a specific day.

Example, Eggs quantity 1 - Breakfast
Eggs quantity 2 - Lunch

For 1 egg, the values will be taken from database as is. How do I make sure values come up for 2 eggs as well? What formula I can use and where? Or should I include food item for different quantities in database itself?

Formula im using right now: VLOOKUP(B2,Calorie_table::Table 1::A2:I10000,5,FALSE)
What do I do to get values for different "quantities" of same food?

I am a learner and new to excel/database - Kindly keep this in mind while explaining me. I have learnt this much by going through posts in this forum

Many thanks
Ahmed

Please find reworked file attached.

I did the following to the file.

In sheet Use this to check Nutri Info I added a column for the quantities you mentioned. They are in column J.

In sheet Calorie_table the data range is now a table named Table1. You can find it in Name Manager.

In Name Manager I you will find a Dynamic Named Range which I called ProductChoices. It’s formula is

Formula:

Please Login or Register  to view this content.

It will shrink or grow as you add / delete items from column A of Calorie_table. This will define the data validation for the in cell drop downs in column B of Use this to check Nutri Info.

The data validation rule for the drop downs is List and Source is ProductChoices.

In B2:I9 of the same sheet I modified the original VLOOKUP formula to accommodate the named table range Table1, the ability to multiply Amounts by the selected records and changed the hard coded column numbers so they automatically advance through the convenience of the COLUMNS function. That formula is

Formula:

Please Login or Register  to view this content.

Select whatever product you like from the drop downs, enter the Amounts in column J and see the results in the output table.

You are welcome. Glad to help, and thank you for the feedback.

FWIW:
If you study up using search terms:

• Dynamic Named Ranges
• Tables
• Data Validation
• and Drop Downs

you will learn much.

Enjoy!

@FlameRetired
Thank you so much for your suggestions. I am reading Ill reply to this thread if I need more explanation please.
I want to make this sheet more user friendly.

Example, right now we are selecting "amounts" as 1 (for 100gms of standard value from main database); 0.5 (for half of that value from database)

I renamed the food items names from Chicken to "Chicken 100gms" - to make it easier for the user to understand that database has stored value of 100gms so he can select a factor accordingly (1 or 0.5 etc)

Now I want to follow an entire different approach:
1. Create a drop down for "amounts" column too where we can select quantity required
2. This dropdown should have values like "100gms, 75gms, 50gms, 5gms, 10gms, 200gms".
just trying to make it noob friendly too.. just drop downs for them to select. What do you think?

or just a drop down with all required factors (1,0.5,0.25, 2, 2.5 etc) - m not sure which one would fit best. But there should be a drop down there in the other column.

and always thanks for your time. You make excel so interesting

ahm3d,

I am not sure I understand.

You have a good thing going when you set up 'Calorie_table' the way you did. It is both simple and flexible.

If I do understand correctly that idea would complicate / duplicate what is already there and working. If doable it would likely be a nightmare to edit / debug, and expose the project to circular references difficult to trace.

Can you tell us more? What is there about the upload in Post #4 that is not user friendly? Would it be more intuitive for the user if column J in 'Use this to check Nutri Info' sheet were moved next to one of the other columns ... say between Quantity and Select Product below? Other?

You're welcome. Thanks for the feedback.

@FlameRetired

Hello everyone and FlameRetired, how have you been?

I had to get back to this thread. Meanwhile, I had learnt a lot about excel.

I pretty much understood how you were able to help me by making a nutritional facts database for food items. I quite understand how formulae and approaches work :D

Now I am stuck at circular referencing.

If you open our last excel, you see that you have created data base using quantities as 1, 0.5, 0.25 etc., for 100g, 50g, 25g respectively.

I am attaching a new sheet here. I want the 'quantity' column to take values in grams and in units and display its nutri values respectively.
My formula is not working as 'quantity' can be 1 for 1 no. (as in 1 egg) and it can also be 100 gms (as in Chicken).

In simple words, instead of putting 1, 0.5, 0.25 etc., for quantities, I want myself to be able to put input as 100, 50, 75 (as in gms) and also 1 (for unit 1 egg).
When Im trying to do it, the calculations come wrong or it gives circular referencing error.

Thanks a bunch!

PS: Why em I unable to attach a document here? can you refer to the previous attachments here and relate my question to it plz? Thanks.