Data Verification Formula - diminishing maximum errors

Hello all-

I have six cells that collectively can equal 8 (decimals are okay, blank cells are fine). The first input can be 8 or less, and as the cells are filled, I would like to diminish/limit the maximum value the user can input in the other cells.

Data Verification-

Allow: Decimal
Data:less than or equal to

Maximum
=(8-SUM($a$1:$c$3))

It seems like the formula is including the active cell input into the SUM verification and rejecting the number (unless the active input number is < remainder of 8-SUM including that input).

Ive also tried a helper cell to calculate the above formula and reference that in the maximum value. I couldnt figure out how to subtract/ignore the input from active cell until a new cell is chosen also.

any corrections or better approaches?

non-VBA solutions please.

TIA

Randall

Thanks Glenn- I didnt see how you applied that formula to the DV the attached file. What am I missing? thank you!

Thx again Glenn.

next issue- I have 6 more cells that can collectively equal 4 BUT the user can only type in those cells after SUM($A$1:$C$3) = 8. I tried:

=IF((SUM($A$1:$D$2)=8),SUM($B$11:$D$13)<=4,0)

and =SUM($A$1:$C$3)=8 && SUM($B$11:$D$13)<=4)

any tips?!?!

TIA Randall

great stuff- thx!

I keep thinking I can extrapolate the above technique, but Im failing on the last 6 cells (18 in total) ....sorry not to ask all this at once

the LAST six cells can be any numbers ONLY if the first 6 cells SUM(a1:c3 )= 8 AND the second 6 cells SUM(b11:c13) = 4... I thought I had it...ugh...help?

thanks again!

Randall

blanks and numbers are fine, in any combo (no max this time) as long as the first 6 cells= 8 and the second 6 cells = 4

thx!

yeah I never would have gotten that!! THX!

so....

when I substitute the example cell ranges for my actual cell ranges, I get an error!

=(SUM($I$13:$I$14,$N$13:$N$14,$S$13:$S$14)=8)*(SUM($J$13:$J$14,$O$13:$O$14,$T$13:$T$14)=4)*NOT(SUMPRODUCT(--ISTEXT($K$13:$K$14,$P$13:$P$14,$U$13:$U$14))>0)

should work right?

got everything working! thank you to all! Im sure Ill be back soon!!

So true...stay tuned and thx!!!!