Ask Your Question

Allow Entry In Cell Based On Value In Another Cell

asked 2017-05-07 18:28:55 +0200

slugger gravatar image

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.

edit retag flag offensive close merge delete


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..

librebel gravatar imagelibrebel ( 2017-05-07 20:11:31 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-05-08 00:29:35 +0200

m.a.riosv gravatar image

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.

image description

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-07 18:28:55 +0200

Seen: 28 times

Last updated: May 08 '17