Random Number Generator based on specific constraints

I am a working on a nutrition spread sheet for people that have to meet certain protein and carbohydrate requirements. On sheet one of the spreadsheet there is a list of different foods all with an ID number. On the second sheet is a meal plan showing what you are supposed to eat. The goal is to eat food that has a combine total of 15-20 grams of protien and 30 grams of carbohydrates per meal. What i want this spreadsheet to do is randomly select items from sheet one that adds together to meet the protein and carbohydrates required. Currently you just look on page one and find the id number of the food you want and type that number into the item# column on sheet two and it automatically fills in the information. The problem is that you end up eating the same foods all the time. Would like to figure out how to program this to randomly select foods that add up to the correct values.

It will be not easy to do it with formulas, but at least you can prepere "a good startpoint for human adjustment".

In your Listing sheet in J7 put random number generated by excel:
=RAND()

and copy down to next 7 cells
in A11 in Monday sheet write a formula:

=IF(AND(SUM(D$10:D10)<500,SUM(E$10:E10)<20,SUM(F$10:F10)<600),100+MATCH(SMALL('Food Listing'!$J$7:$J$14,ROW(1:1)),'Food Listing'!$J$7:$J$14,0),"")

of course adjust allowed sums for D (calories) E (Fat) etc. add also next columns to be checked within AND(...) - carnb and prot.

note that the sum is from row $10 to row 10 - so just one row with no numbers
but as you copy this formula down it will become $10 to 11, $10 to 12 etc.

once one of limits is met - next foods are not selected.

Note, that results are volatile - if you change anything in a sheet (like deleting an empty cell ;-) the sheet will recalculate and you will receive another set of foods.

Do silmilar with other types of food and you can start generating different sets.

Kaper thanks for the help this really helped out.