If preceding cell in a column is blank, block input in current cell.

**Bachi** · 12-06-2011, 11:51 AM

Hello,
I am trying to make a daily interest calculating loan sheet.
All the calculations(interest, payment and final balance) are triggered when the date is input in col. B
The IF function first checks for the date and then checks for any payment made. Only then it calculates the interest upto that day.
Problem:
If I miss a date in one row but continue to enter the date and payments in subsequent rows, the interest calculations are not resumed after "blank date cell"
What I want:
If a date cell is left blank then any data input in subsequent rows should be blocked.

I am not very good a VBA so any such solution will have to be explained thoroughly.
Thanks in advance.
I am attaching the excel sheet

**Fotis1991** · 12-06-2011, 12:12 PM

Hi Bachi.

Starting in B9, select >>Data>>Validation>>Custom and put the formula:

=COUNTA(B8)

Drag down as mutch as you like. Now if there is empty cell, don;t let you to put date in next row.

Hope to helps you.

Regards

**Bachi** · 12-06-2011, 12:32 PM

Originally Posted by Fotis1991

Hi Bachi.

Starting in B9, select >>Data>>Validation>>Custom and put the formula:

=COUNTA(B8)

Drag down as mutch as you like. Now if there is empty cell, don;t let you to put date in next row.

Hope to helps you.

Regards

Hi
thanks.
Two more things.

How do you copy the validation condition from cell B9 upto cell B48
And
After an empty cell, I want to block data input in all subsequent rows, not only subsequent cells in the column.

Thanks again
bachi

**Fotis1991** · 12-06-2011, 01:06 PM

Hi

How do you copy the validation condition from cell B9 upto cell B48
And
After an empty cell, I want to block data input in all subsequent rows, not only subsequent cells in the column.

1) Drag down is the way(right-down of the cell, the sign +)

2) Same way for all the others.

Take a look to the attachement.

Regards

**Bachi** · 12-06-2011, 04:44 PM

Originally Posted by Fotis1991

Hi

1) Drag down is the way(right-down of the cell, the sign +)

2) Same way for all the others.

Take a look to the attachement.

Regards

HI Fotis.
All looks good except that I dont want the cells in column "C" to accept input when the corresponding cell in column "B" is empty.

thanks

**Bachi** · 12-06-2011, 04:48 PM

Originally Posted by Fotis1991

Hi

1) Drag down is the way(right-down of the cell, the sign +)

2) Same way for all the others.

Take a look to the attachement.

Regards

HI Fotis.
All looks good except that I dont want the cells in column "C" to accept input when the corresponding cell in column "B" is empty.

thanks

**Bachi** · 12-06-2011, 11:00 PM

HI Fotis,
I am still having two problems.
1- I dont want the cells in column "C" to accept input when the corresponding cell in column "B" is empty.
2- Data entry is restricted in the cell (column B) which has an empty cell above. However, input is only restricted in the cell just below the empty cell but I can input freely in subsequent cells after that. I wanted the input to be totally restricted even if one cell in col. B is left blank.
thanks again in advance

Originally Posted by Bachi

HI Fotis.
All looks good except that I dont want the cells in column "C" to accept input when the corresponding cell in column "B" is empty.

thanks

**Fotis1991** · 12-08-2011, 09:18 AM

Hi

Because of Forum's problems, just now saw your last message.

I modify the formula and hopes that will be ok for you now.

**Bachi** · 12-08-2011, 06:33 PM

Fotis,

Thanks a lot.
It works fine now. There is a small problem though but I wont bother you for that right now will request you some other time.
I'll tell you the problem so you can think about it.
When I enter dates in a lot of consecutive cells in column "B" and payments in column "C", everything works fine. If I miss even one date cell, input in subsequent cells in Col B and C are blocked.
But If the date is erased in any cell, the payment input does not get erased and stays in the cells in col. "C".

Thanks a lot

Originally Posted by Fotis1991

Hi

Because of Forum's problems, just now saw your last message.

I modify the formula and hopes that will be ok for you now.

**Fotis1991** · 12-09-2011, 04:39 AM

Hi Bachi

My opinion is that the only way to do this, is using VBA and in this case i can not help you.

But i propose you another way, using Conditional Formating and adding an IF function in Column D.

Using this, i think that now it's OK!

Pls, let me know, if it's Ok.

Erase a date in Column A and see the result...