Data Validation Not Executing Criteria from Cell with IF Statement: Alternate Solutions?

[Benedict Afiemo]

Hello. I've searched for similar issues before posting this question and have found nothing yet. Please be kind to me.

Background
My sheet takes two inputs: Contributions and Borrowings. Contributions are made once a month and entered into the sheet; borrowings can be made on demand. Borrowings are to be restricted to double the cumulative amount contributed. However, if there is an existing borrowing, the account holder must reduce it to zero before being allowed to borrow again.

The goal of my sheet is to automatically compute the amount available for borrowing and restrict data entry in a borrowing cell to the max of that amount. Please note that if the account holder has already been loaned, the available borrowing is Zero.

My Attempt
I used Data Validation (max of, decimal), calling the criteria from a key cell. The key cell has this formula: =IF(AD10<>0,0,AF10). The AF column contains the available borrowing amount, computed as twice the amount of cumulative contributions. The AD column contains cumulative borrowings. The Data validation is supposed to make sure the amounts entered for borrowings are no higher than the amount in the key cell, which is either Zero, or double cumulative contributions.

Problem
Data Validation refuses ALL entry into the borrowing cells, even when they meet the criteria. I've tested around and the problem seems to be the feature having a bias against IF statements. As long as there's an IF statement in the key cell, it refuses to allow anything.

Please offer any possible help. Alternate solutions are welcome. I'm also a newbie so pardon if my ignorance is obvious.

Thank You.

 

[liveinhope]

what you need is this
- current borrowings .you already have this in AD10)
- max borrowing if no current borrowing / 2x contribution. you already have this is AF10 .. (but you don't say which cell has "contribution" )

- max borrowing based on current loans based on your formula =IF(AD10<>0,0,AF10. put this is some other cell. I used AH10

- amount user wants to borrow. in (say) AA10 . then validate that entry . the data validation formula must evaluate to either true or false . your formula returns a $$ value. this formula works =AA10<AH10 . If you want to use a cell other than AA10 to enter the "how much user wants to borrow" use than cell reference instead of AA10

 

[Benedict Afiemo]

A debt of gratitude for the prompt reply, liveinhope. But I'm still getting some issues. Here's some more info.

The borrowing cells, 12 of them in even cells from column F,H,J...AB are being summed up in column AD. Column AD is referenced by the key cell in AI(was actually AH before).

It seems to me that the problem

Data Validation refuses ALL entry into the borrowing cells, even when they meet the criteria

is occurring because the validation executes more than once, or is a little delayed in its reaction. That is, when one tries to enter a borrowing that meets the criteria in columns F to AB, the amount is quickly caught up in the total borrowings column AD, which then makes the entry break the criteria causing validation to reject the entry.

I'm not sure my hypothesis is correct, but it sounds reasonable. I suppose you could call it circular referencing or the likes.

Please I would appreciate a workaround or a completely different concept that performs the same function. Pretty please.

 

[liveinhope]

Your "key" cell has the formula that defines how much person can borrow =IF(AD10<>0,0,AF10 (ie if they have a current loan balance 0, if no current load 2x their contributions )

You need to enter the requested amount in a cell other than the formula cell and the cells containing data on contributions and borrowings .

you only need to validate this one cell