Help with drop down list populating mulitple cells, that will react to other variables

Hi Rocco, a very warm welcome to the forum!

I went through your work file. Are you trying to get the details based on the selected food item? Details like unit, fat, carbs, proteins, leucine and stuffs? If this is what you're looking for, then VLOOKUP should be your first choice. This function actually performs a vertical lookup to which you pass 4 parameters that helps you locate what you're looking for..

1. Lookup_Value - is the string that you're searching among a list of table containing the data.
2. Table_Array - is the reference table from which you'll searching the string first and then corresponding details to that row if found.
3. col_index_number - is the column which will contain the result which you desire to retrieve.
4. range_lookup - is a boolean parameter that instructs the function about the type of search you're performing. An approximate or exact match..

Please find your attached re-worked file of yours that shows the same application. Hope, this is what you're looking for.

Hey buddy, that's because its not referring the table from the food chart sheet but from another file named new diet builder2.xlsx which is at your desktop. Just go to Formula > Name Manager and select FoodTable from the list you'll find the address of that. Change it to the right reference.

Please Login or Register  to view this content.

And if you wish to make your food list table dynamic so that you can add extra item entries later in future, you can use dynamic named ranges using OFFSET function in the RefersTo part when creating a named range. Following is the syntax on how you can create:

Please Login or Register  to view this content.

Say if you want to select table from A3:H74, but want the table to be flexible to A3:H75, A3:H76, and so on.

reference : is the point from you start. Say A1.
rows : is the number of rows you wish to jump from the reference. Say 2 to reach A3
cols : is the number of columns you wish to jump from reference. Say 3 if you wish to start the table from D3.

Please note, both rows and cols adds the number to one available for reference. Personally, I prefer setting reference from where I want the table to start and put 0 rows and 0 cols.

[height] - is the field that makes it dynamic. I use COUNT() or COUNTA() function to get the count of filled cells whether numeric or non-numeric. I prefer COUNTA() as it gets the counts of every filled cells. The range for the function is provided vertically, i.e. by columns. It is basically a column with multiple rows.

[width] - is the field that makes it dynamic. Here also we use the same COUNTA or COUNT function but only difference is range is usually the a single row but with multiple columns in it. For instance,

Please Login or Register  to view this content.

Here as we're sure that 8 columns will be there in the table. Please note, in your file you have an extra table below the food table ""Free" foods/condiments". For making the table dynamic you should not have anything after the table as the size grows downwards and towards right. So I usually prefer putting then on the top-left sides or above the table. Hope this helps!