Allow Entry In Cell Based On Value In Another Cell

Kindly look at the table below.

Along A1, A2, A3,……, we have a list of fruits.
Along B1, B2, B3,……, we have a list of the quantities of each item.
Along C1, C2, C3,……, we have the corresponding calorific values of each fruit.
Along the subsequent columns, we enter the number of a particular fruit eaten that day.

image description

How can I ensure that the total number of fruits entered does not exceed the total quantity available, i.e., since we have 3 oranges, all of which have been consumed by Tuesday, if I try to enter an orange on Wednesday it, either, prevents me from entering any value in that cell, or warns me.
Also, how can I ensure that at the bottom, it automatically displays the sum of the calorific value of fruits consumed that day.

Hello slugger, for your second question you could enter a cell formula using SUMPRODUCT().
e.g. in your example for Monday would look like:


For Tuesday =SUMPRODUCT(D2:D5;F2:F5) etc…

Using Menu/Data/Validity from ‘D’ to ‘J’, but take care to intro a zero or clear the cell value before modify a cell value, because the current value in the cell is taken in account for the verification.